Convert text fraction to numeric

[eknotts]

Is there a Function or an easy way to convert a text string in a table such as 2 3/8" (inches) to a numeric decimal number such as 2.375?

 

[EBGreen]

There isn't anything built-in, but it wouldn't be hard to make your own function using the Mid and Instr functions.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[eknotts]

To TomThumbKP - Could you possibly show me a simple example?

 

[GingerR]

You can look up the MID() and INSTR() functions in HELP, plus lots of examples if you search this forum. In order for this to work, your data has to have a strict pattern, such as

Whole Number then Space then Fraction

so you could use INSTR to find the space, use the LEFT() function to then get the characters to the left of the space which will be the whole inches, then get the characters to the right of the space (Right()) and convert that to a decimal. I think it could be a nightmare if you have all kinds of data in there.

what other data do you have? is there also feet? miles? or is it all inches?

I suggest that in the future have users enter the data as decimals, or possibly have separate text boxes where in one they enter a whole number and the other pick a fraction from a combo box (i.e. 1/4", 1/2" ,etc) which you store in a table that has a decimal equivalent associated with it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244