B
bodhi2.71828
I'm trying to write a function to convert a string (e.g. 10' 4-1/2") to
a number. My attempt was to find the ', -, and " characters and work
around them. But I'm getting an error because IsNumeric("1/2") returns
false. Apparently Excel doesn't recognize fractions in strings as
numbers. Any ideas? Thanks. Here's my code.
Public Function ConvertStringToInches(strToConvert As String) As Double
Dim numFeet As Double, numInches As Double, numSubInches As Double
Dim intFootMark As Integer, intInchMark As Integer, intDashMark As
Integer
'find ' and " (could be ' and no " or " and no ')
intFootMark = InStr(1, strToConvert, "'", vbTextCompare)
intInchMark = InStr(1, strToConvert, Chr(34), vbTextCompare)
intDashMark = InStr(1, strToConvert, "-", vbTextCompare)
'get feet
If intFootMark > 0 Then numFeet = Left(strToConvert, intFootMark -
1) Else intFootMark = -1
'get inches and subinches
If intInchMark > 1 Then
If intDashMark > 0 Then
numInches = Mid(strToConvert, intFootMark + 2, intDashMark
- intFootMark - 2)
numSubInches = Mid(strToConvert, intDashMark + 1,
intInchMark - intDashMark - 1)
Else
numInches = Mid(strToConvert, intFootMark + 2, intInchMark
- intFootMark - 2)
End If
End If
'multiply feet by 12 and add together
ConvertStringToInches = numFeet * 12 + numInches + numSubInches
End Function
a number. My attempt was to find the ', -, and " characters and work
around them. But I'm getting an error because IsNumeric("1/2") returns
false. Apparently Excel doesn't recognize fractions in strings as
numbers. Any ideas? Thanks. Here's my code.
Public Function ConvertStringToInches(strToConvert As String) As Double
Dim numFeet As Double, numInches As Double, numSubInches As Double
Dim intFootMark As Integer, intInchMark As Integer, intDashMark As
Integer
'find ' and " (could be ' and no " or " and no ')
intFootMark = InStr(1, strToConvert, "'", vbTextCompare)
intInchMark = InStr(1, strToConvert, Chr(34), vbTextCompare)
intDashMark = InStr(1, strToConvert, "-", vbTextCompare)
'get feet
If intFootMark > 0 Then numFeet = Left(strToConvert, intFootMark -
1) Else intFootMark = -1
'get inches and subinches
If intInchMark > 1 Then
If intDashMark > 0 Then
numInches = Mid(strToConvert, intFootMark + 2, intDashMark
- intFootMark - 2)
numSubInches = Mid(strToConvert, intDashMark + 1,
intInchMark - intDashMark - 1)
Else
numInches = Mid(strToConvert, intFootMark + 2, intInchMark
- intFootMark - 2)
End If
End If
'multiply feet by 12 and add together
ConvertStringToInches = numFeet * 12 + numInches + numSubInches
End Function