Need Formula/Help for separating numbers from random text cells

[gaminguy]

Data.jpg


Hi there

As seen in the attached file, I need to seperate the numbers from text in one go, because this is just an example and i need to seperate numbers from a lot of cells.

I have tried text to coulnms and =right, =left and = substitue formulas... But these does not works because of unique nature of the data, as number in each cell is placed randomly and there are no spaces or common factor between number and text.

Please i need your assistance.

[Ace_XL]

Use

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Courtesy: oldchippy
http://www.excelforum.com/excel-work...ic-string.html

[gaminguy]

use

=lookup(99^99,--("0"&mid(a1,min(search({0,1,2,3,4,5,6,7,8,9},a1&"0123456789")),row($1:$10000))))

courtesy: Oldchippy
http://www.excelforum.com/excel-work...ic-string.html

oh boy its too difficult to understand, isn't there any easy way to do this?

[martindwilson]

i think that's ok