Often, stock data you obtain from information services, or data entered into a spreadsheet will be in fractional format. For example, "38 3/8" to mean 38.375.
Here is a formula that will take any fractional expression of a number in cell A1 and convert it to decimal format that Claris Resolve can use:
= Value(Left(A1,Find(" ",A1,1)))+(Value(Left(Right(A1,Len(A1)-Find(" ",A1,1)), Find("/",Right(A1,Len(A1)-Find(" ",A1,1)),1)-1))/Value(Right(Right(A1,Len(A1)-Find(" ",A1,1)), Len(Right(A1,Len(A1)-Find(" ",A1,1)))-Find("/",Right(A1,Len(A1)-Find(" ",A1,1)),1))))
Here's how we built the formula:
raw text expression of number (a) = "38 3/8"
whole part of the number (w) = Left(a,Find(" ",a,1))
fractional part of the number (f) = Right(a,Len(a)-Find(" ",a,1))
numerator in the fraction (n) = Left(f,Find("/",f,1)-1)
denominator of the fraction (d) = Right(f,Len(f)-Find("/",f,1))
decimal version = Value(w)+(Value(n)/Value(d))
To find "w" we use two Claris Resolve text slicing functions to take the portion of the text string that appears before the first space in the number. "f" takes everything after the first space. "n" takes everything before the slash ("/") in "f" and considers it the numerator. "d" takes everything after the slash in "f." We put it together in "d," which has to convert the text values returned by the components and convert them to arithmetic values.
The first formula above is just everything put together.