×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Feet-Inches Text To Number Macro

Feet-Inches Text To Number Macro

Feet-Inches Text To Number Macro

(OP)
I get an excel report from 3D modeling software which gives center of gravity coordinates as text i.e. 6'-2". Is there an existing macro to convert this to a number, preferably in inches - 6'-2" becomes 74.

RE: Feet-Inches Text To Number Macro

Dear PMarinshaw

custom Excel function attached. May be useful to you.
String will be converted to feet.

Thanks
Nitin Patel

"Treat every one with politeness even those who are rude to you, Not because they are not nice but because you are nice........"



Function STOF(FinSTR As String)

FinSTR = Trim(FinSTR)
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) '  """""""

If Len(FinSTR) > posF And posI = 0 Then  ' if no """"""" add it to end of string
    FinSTR = FinSTR & Chr(34)
End If

posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) '  """""""

If posF = 0 Then
    feetSTR = 0#
Else
    feetSTR = Val(Trim(Left(FinSTR, posF - 1)))
End If

inchSTR = Trim(Right(FinSTR, Len(FinSTR) - posF))

If Len(inchSTR) > 0 Then
    posS = InStr(1, inchSTR, "/", vbTextCompare)
    posB = InStr(1, inchSTR, Chr(32), vbTextCompare)
    posD = InStr(1, inchSTR, "-", vbTextCompare)
    
    If posB = 0 And posD = 0 And posS = 0 Then
        inchSTR = Val(inchSTR)
    End If
    
    If posB = 0 And posD = 0 And posS > 0 Then
        posS = InStr(1, inchSTR, "/", vbTextCompare)
        inchSTR2A = Trim(Left(inchSTR, posS - 1))
        inchSTR2B = Trim(Right(inchSTR, Len(inchSTR) - posS))
        inchSTR = Val(inchSTR2A) / Val(inchSTR2B)
    End If

    If posB > 0 And posS > 0 Then
        inchSTR1 = Left(inchSTR, posB - 1)
        inchSTR2 = Right(inchSTR, Len(inchSTR) - posB)
        posS = InStr(1, inchSTR2, "/", vbTextCompare)
        inchSTR2A = Left(inchSTR2, posS - 1)
        inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
        inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
    End If
    
    If posD > 0 And posS > 0 Then
        inchSTR1 = Left(inchSTR, posD - 1)
        inchSTR2 = Right(inchSTR, Len(inchSTR) - posD)
        posS = InStr(1, inchSTR2, "/", vbTextCompare)
        inchSTR2A = Left(inchSTR2, posS - 1)
        inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
        inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
    End If

Else
    
    inchSTR = 0#
    
End If

STOF = feetSTR + (inchSTR / 12#)

End Function
 

RE: Feet-Inches Text To Number Macro

Slight modification required to suite your input.
Revised function.

Hope this will work

Nitin patel


Function STOF(FinSTR As String)

FinSTR = Trim(FinSTR)
posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) '  """""""

If Len(FinSTR) > posF And posI = 0 Then  ' if no """"""" add it to end of string
    FinSTR = FinSTR & Chr(34)
End If

posF = InStr(1, FinSTR, Chr(39), vbTextCompare) ' ''''''''
posI = InStr(1, FinSTR, Chr(34), vbTextCompare) '  """""""

If posF = 0 Then
    feetstr = 0#
Else
    feetstr = Val(Trim(Left(FinSTR, posF - 1)))
End If

If feetstr <> 0 Then
    posF = Application.WorksheetFunction.Max(InStr(1, FinSTR, Chr(39), vbTextCompare), InStr(1, FinSTR, "-", vbTextCompare))
    inchSTR = Trim(Right(FinSTR, Len(FinSTR) - posF))
Else
    inchSTR = Trim(FinSTR)
End If

If Len(inchSTR) > 0 Then
    posS = InStr(1, inchSTR, "/", vbTextCompare)
    posB = InStr(1, inchSTR, Chr(32), vbTextCompare)
    posD = InStr(1, inchSTR, "-", vbTextCompare)
    
    If posB = 0 And posD = 0 And posS = 0 Then
        inchSTR = Val(inchSTR)
    End If
    
    If posB = 0 And posD = 0 And posS > 0 Then
        posS = InStr(1, inchSTR, "/", vbTextCompare)
        inchSTR2A = Trim(Left(inchSTR, posS - 1))
        inchSTR2B = Trim(Right(inchSTR, Len(inchSTR) - posS))
        inchSTR = Val(inchSTR2A) / Val(inchSTR2B)
    End If

    If posB > 0 And posS > 0 Then
        inchSTR1 = Left(inchSTR, posB - 1)
        inchSTR2 = Right(inchSTR, Len(inchSTR) - posB)
        posS = InStr(1, inchSTR2, "/", vbTextCompare)
        inchSTR2A = Left(inchSTR2, posS - 1)
        inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
        inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
    End If
    
    If posD > 0 And posS > 0 Then
        inchSTR1 = Left(inchSTR, posD - 1)
        inchSTR2 = Right(inchSTR, Len(inchSTR) - posD)
        posS = InStr(1, inchSTR2, "/", vbTextCompare)
        inchSTR2A = Left(inchSTR2, posS - 1)
        inchSTR2B = Right(inchSTR2, Len(inchSTR2) - posS)
        inchSTR = Val(inchSTR1) + Val(inchSTR2A) / Val(inchSTR2B)
    End If

Else
    
    inchSTR = 0#
    
End If

STOF = feetstr + (inchSTR / 12#)

End Function

 

RE: Feet-Inches Text To Number Macro

Try this one:

=LEFT(A1,SEARCH("'",A1)-1)*12+MID(A1,SEARCH("-",A1,2)+1,LEN(A1)-SEARCH("-",A1,2)-1)

ft-in text (like 6'-2" or 6'-2 1/8") is in cell A1

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources