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

Carchee · #6 09-17-2014, 02:05 PM

Okay Paul,

I've gone through and tried to add other conversions and all have failed. The attached sample is all conversion that I need. I thought it would be easy to add the others in but for some reason I can't get them to work, obviously it's my lack of coding knowledge.

All inch (") units need to be converted to a nominal pipe diameter which means a simple formula is close but isn't going to work. You'll see on the attachment also that a lot of those inch dimensions contain dashes (-) and a forward slash (/). The pipe sizes never change so the code could recognize the pipe size (essentially text) and then convert it to the nominal pipe size. The attachment shows some example of that. I tried finding ways to do this but couldn't find anything.

One question about your code: What does

Code:

And j < k Then

do exactly. The way I understand your code is first it searches the right most part of the cell for numbers and converts those numbers then it moves onto the second right most number part within the cell and it look like the "And j < K Then" is telling it to look at the second part. Am I far off?

Here is my attempt to add my own code.....be nice

Code:

Sub EQLConverter()
Dim lRow As Long, i As Long, j As Long, k As Long, StrTmp 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)
        'See what the 'word' ends with
        Select Case Right(StrTmp, 1)
          'If it ends with ', try a ft:m conversion
          Case "'"
            StrTmp = Split(StrTmp, "'")(0)
            If IsNumeric(StrTmp) Then
              StrOut = StrOut & " " & Format(StrTmp * 0.3048, "0.0") & "m" & " (" & StrTmp & "')"
            Else
              StrOut = StrOut & " " & StrTmp
            End If
            
         '*******INCH TO MM CONVERSION (NOMINAL DIAMETERS, NO FORMULAS)*******
            
          'If it ends with ", try a in:mm conversion
         ' Case """"
           ' StrTmp = Split(StrTmp, """")(0)
           ' If IsNumeric(StrTmp) Then
            '  StrOut = StrOut & " " & Format(StrTmp * 25.4, "0.0") & "mm" & " (" & StrTmp & """)"
           ' Else
             ' StrOut = StrOut & " " & StrTmp
           ' End If
           
           '*******TRYING AREA CONVERSIONS****
           
           
         'If it ends with 2, try a FT^2:M^2 conversion
            ' Case "2"
           ' StrTmp = Split(StrTmp, "2")(0)
           ' If IsNumeric(StrTmp) Then
             ' StrOut = StrOut & " " & Format(StrTmp * 0.0929, "0.0") & "M^2" & " (" & StrTmp & "FT^2)"
           ' Else
              'StrOut = StrOut & " " & StrTmp
           ' End If
           'If it ends with ., try a FT^2:M^2 conversion
          'Case "."
            'StrTmp = Split(StrTmp, ".")(0)
            'If IsNumeric(StrTmp) Then
              'StrOut = StrOut & " " & Format(StrTmp * 0.929, "0.0") & "M^2" & " (" & StrTmp & "SQ. FT.)"
            'Else
              'StrOut = StrOut & " " & StrTmp
            'End If
         'If it ends with ), try a FT^2:M^2 conversion
         ' Case ")"
            'StrTmp = Split(StrTmp, ")(0)
            'If IsNumeric(StrTmp) Then
             ' StrOut = StrOut & " " & Format(StrTmp * 0.929, "0.0") & "M^2" & " (" & StrTmp & "FT^2)"
           ' Else
              'StrOut = StrOut & " " & StrTmp
           ' End If
           
            '***************
                 
            
          Case Else
            'If it's not ft/in, look for other possibilities
            If IsNumeric(StrTmp) And j < k Then
              'If the next 'word' is GPM, do a GPM:CMH conversion
              If Split(.Text, " ")(j + 1) = "GPM" Then
                StrOut = StrOut & " " & Format(StrTmp / 1000 * 3.78544 * 60, "0.0") & " CMH" & " (" & StrTmp & " GPM)"
                j = j + 1
              'If it ends with GAL, try a GAL:M^3 conversion
            ElseIf Split(.Text, " ")(j + 1) = "GAL" Then
                StrOut = StrOut & " " & Format(StrTmp * 0.003785, "0.0") & " M^3" & " (" & StrTmp & " GAL)"
                j = j + 1
             'If it ends with FT^2, try a FT^2:M^2 conversion
            ElseIf Split(.Text, " ")(j + 1) = "FT^2)" Then
                StrOut = StrOut & " " & Format(StrTmp * 0.0929, "0.0") & " M^2" & " (" & StrTmp & " FT^2)"
                j = j + 1
               Else
              StrOut = StrOut & " " & StrTmp
            End If
             Else
              StrOut = StrOut & " " & StrTmp
            End If
            
        End Select
      Next
        .Value = StrOut
    End With
  Next
End With
End Sub

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