Search a cell that contains words and numbers and convert the numbers to metric

macropod · #16 09-24-2014, 06:51 PM

Try the following update. Note that I've retained the pressure conversions in the code, but they're commented-out so they won't do anything. That means you can reinstate them later on if need be.

Code:

Sub EQLConverter()
Application.ScreenUpdating = False
Dim lRow As Long, i As Long, j As Long, k As Long
Dim StrTmp As String, StrBit As String, StrConv As String, StrOut As String
With ActiveSheet.UsedRange
  'Loop through each cell in column C
  For i = 3 To .Cells.SpecialCells(xlCellTypeLastCell).Row
    With .Cells(i, 3)
      StrOut = ""
      'Split the cell results by spaces for parsing
      k = UBound(Split(.Text, " "))
      'Process each 'word'
      For j = 0 To k
        StrTmp = Split(.Text, " ")(j)
        If Len(StrTmp) > 0 Then
          'See what the 'word' ends with
          Select Case UCase(Right(StrTmp, 1))
            'If it ends with ', try a ft:m conversion
            Case "'"
              StrConv = Replace(Split(StrTmp, "'")(0), "(", "")
              If IsNumeric(StrConv) Then
                StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrTmp & ")"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            'If it ends with ", try an in:mm conversion
            Case """"
              StrConv = Replace(Split(StrTmp, """")(0), "(", "")
              StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
              If IsNumeric(StrConv) Then
                StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & ")"
              Else
                StrOut = StrOut & " " & StrTmp
              End If
            Case Else
              Select Case Right(StrTmp, 2)
                'If it ends with FT, try an ft:m conversion
                Case "FT"
                  StrConv = Replace(Split(StrTmp, "FT")(0), "(", "")
                  If IsNumeric(StrConv) Then
                    StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrConv & "')"
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
                'If it ends with IN, try an in:mm conversion
                Case "IN"
                  StrConv = Replace(Split(StrTmp, "IN")(0), "(", "")
                  If IsNumeric(Left(StrConv, 1)) Then
                    StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
                  End If
                  If IsNumeric(StrConv) Then
                    StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrConv & """)"
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
                Case Else
                  'If it's not ft/in and we haven't reached the end, look for other possibilities
                  If j < k Then
                    StrConv = Replace(Split(StrTmp, """")(0), "(", "")
                    If IsNumeric(StrConv) Then
                      'If the next 'word' is IN/INCH, do an in:mm conversion
                      If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then
                        StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
                        StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & " mm" & " (" & StrConv & """)"
                        j = j + 1
                      'If the next 'word' is GPM, do a GPM:CMH conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPM" Then
                        StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " L/Min" & " (" & StrConv & " GPM)"
                        j = j + 1
                      'If the next 'word' is GAL, do a Gal:Ltr conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GAL" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " Ltr" & " (" & StrConv & " GAL)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " Ltr" & " (" & StrConv & " GAL)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " Ltr" & " (" & StrConv & " GAL)"
                        End Select
                        j = j + 1
                      'If the next 'word' is GPD, do a GPD:LPD conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPD" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPD" & " (" & StrConv & " GPD)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPD" & " (" & StrConv & " GPD)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPD" & " (" & StrConv & " GPD)"
                        End Select
                        j = j + 1
                      'If the next 'word' is G/HR, do a G/HR:L/HR conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 4)) = "G/HR" Then
                        Select Case StrConv * 3.785412
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPH" & " (" & StrConv & " GPH)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPH" & " (" & StrConv & " GPH)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPH" & " (" & StrConv & " GPH)"
                        End Select
                        j = j + 1
                      'If the next 'word' is LB, do a LB:KG conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "LB" Then
                        Select Case StrConv / 2.20462
                          Case Is >= 100
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") * 10 & " KG" & " (" & StrConv & " LB)"
                          Case Is >= 10, Is < 100
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") & " KG" & " (" & StrConv & " LB)"
                          Case Else
                            StrOut = StrOut & " " & Format(StrConv / 2.20462, "0.0") & " KG" & " (" & StrConv & " LB)"
                        End Select
                        j = j + 1
                      'If the next 'word' is FT^2, do a FT^2:M^2 conversion
                      ElseIf UCase(Replace(Split(.Text, " ")(j + 1), ")", "")) = "FT^2" Then
                        StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                        j = j + 1
                      'If the next two 'words' are SQ. FT., do a FT^2:M^2 conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "SQ" And UCase(Left(Split(.Text, " ")(j + 2), 2)) = "FT" Then
                        StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
                        j = j + 2
                      'If the next 'word' is PSI, do a PSI:KPA conversion
                      ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "PSI" Then
                        StrOut = StrOut & " " & Round(StrConv * 6.894757, 0) & " KPA" & " (" & StrConv & " PSI)"
                      Else
                        StrOut = StrOut & " " & StrTmp
                      End If
                    ' Test for a cell containing a numeric range
                    'ElseIf InStr(StrConv, "-") > 0 Then
                      'If IsNumeric(Split(StrConv, "-")(0)) And IsNumeric(Split(StrConv, "-")(1)) Then
                        'If the next 'word' is PSI, do a PSI:KPA conversion
                        'If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 6.894757, 0) & "-" & Round(Split(StrConv, "-")(1) * 6.894757) & " KPA" & " (" & StrConv & " PSI)"
                          'j = j + 1
                        'End If
                        'If the next 'word' begins with IN or Hg, do an in:mm conversion
                        'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "HG" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """) Hg"
                          'j = j + 2
                        'End If
                        'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then
                          'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """)"
                          'j = j + 2
                        'End If
                      'End If
                    Else
                      StrOut = StrOut & " " & StrTmp
                    End If
                  Else
                    StrOut = StrOut & " " & StrTmp
                  End If
              End Select
          End Select
        Else
          StrOut = StrOut & " "
        End If
      Next
      StrOut = Trim(StrOut)
      If InStr(StrOut, " X ") > 0 Then
        StrConv = ""
        For j = 0 To UBound(Split(StrOut, " X ")) Step 2
          StrTmp = Split(StrOut, " X ")(j)
          For k = 0 To UBound(Split(StrTmp, " ")) - 1
            StrConv = StrConv & " " & Split(StrTmp, " ")(k)
          Next
          StrConv = StrConv & " * " & Split(Split(StrOut, " X ")(j + 1), " ")(0)
          StrConv = StrConv & " " & Replace(Split(StrTmp, " ")(k), ")", " x ")
          StrConv = StrConv & Replace(Split(Split(StrOut, " X ")(j + 1), " ")(1), "(", "")
        Next
        For j = 1 To UBound(Split(StrOut, " X ")) Step 2
          StrTmp = Split(StrOut, " X ")(j)
          For k = 2 To UBound(Split(StrTmp, " "))
            StrConv = StrConv & " " & Split(StrTmp, " ")(k)
          Next
        Next
      StrOut = StrConv
      End If
      .Value = Trim(StrOut)
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub

Carchee · #17 09-25-2014, 08:12 AM

I'm not getting any errors which is nice and everything that I need converted converts. Paul, thank you so much for your help. I wish I could learn this stuff quicker so I wouldn't have to rely on you so much, it's slow for me but I'm gaining more knowledge each time I do a project like this. Thank you thank you.

Carchee · #18 09-30-2014, 12:35 PM

Okay Paul, I inserted this code into our overall spreadsheet which contains several different tabs and multiple programs that we've written over the years. Integrating this code into our spreadsheet gives me errors. I spent a day thoroughly going through debugging procedures to pinpoint when and where the error arises. I know this was supposed to be solved but new issues have arisen. Should I create a new thread?

macropod · #19 09-30-2014, 02:14 PM

Continue in this thread - I'll remove the 'solved' tag for now.

Carchee · #20 09-30-2014, 04:11 PM

I'm getting the "Run-time error '438': Object doesn't support this property or method", with this line highlighted:

Code:

                Case "IN"
                  StrConv = Replace(Split(StrTmp, "IN")(0), "(", "")
                  If IsNumeric(Left(StrConv, 1)) Then
                    StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))

I tried changing the "IN" to the " symbol bit then it errors in the same manner at:

Code:

            Case """"
              StrConv = Replace(Split(StrTmp, """")(0), "(", "")
              StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))

When I delete the basket strainer out of the list and run the program it errors on the Thermometer, at:

Code:

                Case Else
                  'If it's not ft/in and we haven't reached the end, look for other possibilities
                  If j < k Then
                    StrConv = Replace(Split(StrTmp, """")(0), "(", "")
                    If IsNumeric(StrConv) Then
                      'If the next 'word' is IN/INCH, do an in:mm conversion
                      If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then
                        StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))

The code works perfectly when I delete both the basket strainer and the Thermometer.

I have tried modifying the list and some of the code to see if I can fix it but am unable to. I have researched the error a bit but can't find any info directly correlated with my issue. Thanks again.

macropod · #21 09-30-2014, 09:23 PM

What does the worksheet row on which the error occurs contain?

Carchee · #22 10-01-2014, 07:17 AM

It's the Basket Strainer "MERMADE, 6IN X 3IN ECCENTRIC REDUCING STRAINER" and the other one is the Thermometer "TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL".

Also I had a question about the code. What does the "*12+0" do in that line that's getting the error? I know its for the Replace as String part but what exactly does it do? I noticed that if I change it to any number or equation it still works.

macropod · #23 10-01-2014, 02:51 PM

Quote:

Originally Posted by Carchee

It's the Basket Strainer "MERMADE, 6IN X 3IN ECCENTRIC REDUCING STRAINER"

I don't gaet any errors with that one - it outputs:
MERMADE, 150mm * 75mm (6" x 3") ECCENTRIC REDUCING STRAINER

Quote:

Originally Posted by Carchee

the other one is the Thermometer "TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL".

In post #14, I said:

Quote:

Originally Posted by macropod

You'll need to tidy up more of your data, too. For example, you should remove any existing metric figures - you wouldn't want a double conversion for:
TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL
In this case, I'd suggest deleting the (180mm). You might also want to add a 7in:180mm conversion to your diameters. The output would then become:
TRERICE AX9, 180 mm (7") ADJUSTABLE ANGLE W/ THERMOWELL
which is consistent with how the other conversions are done.

The other problem with that particular item is that the incorrect spacing around the (180mm) would cause problems anyway.

Quote:

Originally Posted by Carchee

Also I had a question about the code. What does the "*12+0" do in that line that's getting the error? I know its for the Replace as String part but what exactly does it do?

What is does is to take a string that might be expressed as, say:
1' 3"
and replace the ' with *12+0, so you end up with:
1*12+03
for a feet_&_inches:inches conversion (in this case, 15in), so the in:mm conversion can be done.

Carchee · #24 10-01-2014, 04:04 PM

Ah that explanation for the *12+0 makes perfect sense, thank you.

This is where it's going to get tough because I've now added the code into a whole new environment which you won't be able to simulate. None of these errors occur in my original spreadsheet, like one that you would be using - a stand alone spreadsheet with no other code, but with it now integrated into this new spreadsheet it comes up with these errors.

I have deleted all metric units within the list, that is something we can easily alter. I shouldn't get any issues with that. I also added the 7in:180mm conversion to the diameters.

Do you know why this error would show up for any reason given the code used and parameters set? This may be difficult because you are unable to recreate the error.

I have searched for reasons this error would come up and nothing I have read compares to my situation.

macropod · #25 10-01-2014, 11:33 PM

Since I have no idea what your other code might be doing, it's rather difficult to give advice on how to address any issues thus created. A workaround might be to copy the data to another workbook containing only the code I've provided, run my macro, then copy the data back again. That way, any interactions with whatever your existing code does could be eliminated.

Carchee · #26 10-02-2014, 09:57 AM

I got the error to go away! The error was occurring on the code line:

Code:

StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))

This line of code looks for a dimension that is in feet and inches however the only place feet is ever used is in the pump callout which is already converted. The rest of the diameter dimensions are all called out in inches. So a 1'-6" pipe is called out as an 18" pipe, always (just a standard for this list). Because of this I was able to comment out that line of code (all three times it shows up) and now I don't get the error.

I decided to run one final run and found another component that I was omitting (we delete equipment that we're not using for the specific job from the list). I will send you the list with no equipment deleted.

The Chemical Controller model is being converted into inches. You'll see what I mean when you insert it. The only way I can think of getting around it is by adding a space after the number and before the comma. This works but it doesn't look good and it's not grammatically correct. I would like to leave it as is without it converting. Watching the Locals window it converts it because StrTmp (250,) and (2000,) are greater than 0:

Code:

 For j = 0 To k
        StrTmp = Split(.Text, " ")(j)
        If Len(StrTmp) > 0 Then

Any ideas how to get around this without adding a space?

Also I tried finding a thread about superscripting in vba but couldn't find anything. Not sure if this is the way I want to go but is it possible? Specifically I'm talking about superscripting the '2' in the ft^2 for the filter or others that might show up.

Carchee · #27 10-02-2014, 10:54 AM

Now when I comment out those three lines of code some of the " (inch) dimensions aren't converting.

Code:

Case """"
              StrConv = Replace(Split(StrTmp, """")(0), "(", "")
              'StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
              If IsNumeric(StrConv) Then
                StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & ")"
              Else
                StrOut = StrOut & " " & StrTmp
              End If

Specifically the line of code commented out above. Is there some way of modifying this line of code so it's not looking for "-" and replacing with "+" and still have it function?

macropod · #28 10-02-2014, 03:39 PM

To handle strings like:
CHEMTROL 250, INCLUDE INDUSTRIAL PROBES
insert:
'If a ',', skip it
Case ",": StrOut = StrOut & " " & StrTmp
after:
Select Case UCase(Right(StrTmp, 1))

You might also want to add some order-of-magnitude roundings for the GPM, too.

As for the superscripting, the simplest solution would be to use SQ.M, SQ.FT, etc, instead. Otherwise, because text strings have no formatting, what is required is for the code to locate each ^2 sequence in the cell after it's been re-populated, select those characters and superscript them. You can do that with code like:

Code:

Sub Superscript(Rng As Range)
Dim ArrSupr As Variant, i As Long, j As Long, k As Long
ArrSupr = Array("^2", "^3")
For i = LBound(ArrSupr) To UBound(ArrSupr)
  With Rng
    j = 1
    Do While j > 0
      k = Len(ArrSupr(i))
      j = InStr(j, .Value, ArrSupr(i), vbTextCompare)
      If j > 0 Then
        .Characters(Start:=j, Length:=k).Font.Superscript = True
        j = j + k
      End If
    Loop
  End With
Next
End Sub

which you'd call by inserting:
Call Superscript(.Cells(i, 3))
before the final:

Code:

  Next
End With

Carchee · #29 10-03-2014, 01:07 PM

The code for skipping Case"," worked perfectly. The Sub Superscript also worked great, that was neat....now to see what we want our standard to be. Thanks again.

Any advise for post #26 and #27 regarding modification to the line that gives me an error?

macropod · #30 10-03-2014, 02:12 PM

With the mods in post #28, I don't see any errors - and that's with the three lines:
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
still in the code.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Words in my document were converted to numbers????	MikeD23	Word	1	08-26-2012 11:09 AM
can word convert numbers?	koolfire	Word	2	01-07-2012 02:12 PM
Convert numbers to a specific text string	francis	Excel	1	10-06-2011 01:43 PM
Numbers in Words	janak	Excel	2	12-19-2010 08:53 PM
Numbers Convert in word in MS Access	towhid	Office	0	08-19-2010 01:17 AM