getting the first number from a text box

the walker · January 23, 2003

is it possible to extract the first number from a text box by this i have a long list of water heater capacity which vary in content from '10 - 15 litres','<15 litres' 'and unpressurised 10 - 100 litres'. and i really need to sort by the capacity size, but this really does not work with it all being mixed with bits of text.

what i would like is to be able to have a calc field that equals the first number it find in the text

help

Cheers

djgogi · January 23, 2003

TextToNum(Case(Length(TextToNum(text)),LeftWords(Right(

text,

Min(

Case(Position(text, "1", 1, 1),Position(text, "1", 1, 1),64000),

Case(Position(text, "2", 1, 1),Position(text, "2", 1, 1),64000),

Case(Position(text, "3", 1, 1),Position(text, "3", 1, 1),64000),

Case(Position(text, "4", 1, 1),Position(text, "4", 1, 1),64000),

Case(Position(text, "5", 1, 1),Position(text, "5", 1, 1),64000),

Case(Position(text, "6", 1, 1),Position(text, "6", 1, 1),64000),

Case(Position(text, "7", 1, 1),Position(text, "7", 1, 1),64000),

Case(Position(text, "8", 1, 1),Position(text, "8", 1, 1),64000),

Case(Position(text, "9", 1, 1),Position(text, "9", 1, 1),64000)

)

), 1)))

Firrst thing I can think about

Dj

the walker · January 23, 2003

that seems to work for a few off them, still trying to see a pattern between the ones that are not working and the ones that are.

only thing i can make out is that it does not seem to work on the ones with the numbers at the start of the text ie '100 litres' or '10 - 20 lt' its not working on '<30' but it works fine on '< 30' or 'unpressurised 10 - 20'

confused

BobWeaver · January 23, 2003

Here is a formula that I sometimes use:

TextToNum (LeftWords(Replace(" " & TextField, 1, Position(

Substitute(Substitute(Substitute(Substitute(Substitute(

Substitute(Substitute(Substitute(Substitute(Substitute(TextField,

"0", "~"),

"1", "~"),

"2", "~"),

"3", "~"),

"4", "~"),

"5", "~"),

"6", "~"),

"7", "~"),

"8", "~"),

"9", "~")

, "~", 1, 1), "")

,1))

Note, the quoted text immediately following 'Replace(' is a single space. I use a tilde (~) character in the formula because it is unlikely to occur naturally in the text. However, if it does, just replace it with another uncommon character wherever it appears in the formula.

djgogi · January 24, 2003

The right formula is

TextToNum(Case(Length(TextToNum(text)),LeftWords(Right(

text,

Length(text)-Min(