extracting numbers from free form text field

[landmark]

Is there any easy way to extract numbers from a free form text field?
For example:

123 abc def
abc 456 def
abc def 789

The results I'm looking for are:

123
456
789

The alpha characters and the numbers are randomly disbursed in th
field . I'm looking for one cool formula that would extrac
the number from the field whereever the number resides within the field
Thanks in advance.

landmar

 

[Frank Kabel]

Hi
try the following array formula:
=--MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,seq,1)),0),ROWS(seq)-SUM(--ISERRO
R(-MID(A1,seq,1))))

where seq is the defined name =ROW(INDIRECT("1:1024")

or as non-array alternative:
=IF(ISNUMBER(LOOKUP(2,1/MID(A1,seq,1),seq)),MID(A1,LOOKUP(2,1/MID(A1,10
24-seq,1),1024-seq),LOOKUP(2,1/MID(A1,seq,1),seq)-LOOKUP(2,1/MID(A1,102
4-seq,1),1024-seq)+1),"")

 

[JulieD]

Hi Frank

wow!!! not sure i'll ever understand these formulas ... but they seem to
work great - however the defined name needs an additional ) as in

where seq is the defined name =ROW(INDIRECT("1:1024")

should be
=ROW(INDIRECT("1:1024"))

Cheers
JulieD

 

[Frank Kabel]

H Julie
good spot. Thanks for the correction