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

I hacked my way through this using:

Private Function FractionToNumeric(strToConvert As String) As Double
Dim intNumerator As Integer, intDenominator As Integer,
intSlashLocation As Integer
intSlashLocation = InStr(1, strToConvert, "/", vbTextCompare)
If intSlashLocation 0 Then
intNumerator = Left(strToConvert, intSlashLocation - 1)
intDenominator = Right(strToConvert, Len(strToConvert) -
intSlashLocation)
FractionToNumeric = intNumerator / intDenominator
End If
End Function

But it still seems like there should be an easier way to do this. I
feel like I must be reinventing the wheel here ....

bodhi,
You could try Evaluate("1/2")

NickHK

wrote in message
oups.com...
I hacked my way through this using:

Private Function FractionToNumeric(strToConvert As String) As Double
Dim intNumerator As Integer, intDenominator As Integer,
intSlashLocation As Integer
intSlashLocation = InStr(1, strToConvert, "/", vbTextCompare)
If intSlashLocation 0 Then
intNumerator = Left(strToConvert, intSlashLocation - 1)
intDenominator = Right(strToConvert, Len(strToConvert) -
intSlashLocation)
FractionToNumeric = intNumerator / intDenominator
End If
End Function

But it still seems like there should be an easier way to do this. I
feel like I must be reinventing the wheel here ....