[Return to contents]

## Calculations on numbers in a text field (eg imperial measurements such as 3'6")

Last updated: 12 Jul 2002

If you want to do calculations on numbers contained within a text, you first have to get the number out of the text field and then do the calculations. To do this you will have to use the Position function to locate where the single and double quotes are in the text string, and the Left and Mid functions to extract the numbers. Then you do your calculations. Then you can use the results of your calculations to create a new text string if that is what you need (using the Combine function).

This could all be done in the one formula, but it would be a long a complicated one!

Here is an example of this process spread over 4 formulas to make it more readable. The original measurement is contained i a text field called 'size'. It calculates half the length contained in 'size' and puts the result in 'halfsizedtext'. It should work regardless of how many digits or decimal places are in the original measurement (eg 3' 6", or 123' 4.57")

- feet = Left(size, Position(size, Chr(39), 1) - 1)

- inches = Middle(size, Position(size, Chr(39), 1) + 1, Position(size, Chr(34), 1) - Position(size, Chr(39), 1) - 1)

- halfsize = ((feet * 12) + inches) / 2

- halfsizetext = Combine(Trunc(halfsize / 12, 0), Chr(39), ' ', halfsize - (Trunc(halfsize / 12, 0) * 12), Chr(34))

*© Copyright,
JohnBrown,
Trademarks,
Disclaimer,
Acknowledgements.*