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

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

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

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.

The right formula is

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

text,

Length(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

), 1)))

Sorry for the glitch, didn't test it at all.

Dj

that works like a charm

i dont know where you come up with the formula for something like this

Genius

Thank you