Formula to display just number and not text

[edeaston1983]

Hi,

I have looked through other peoples questions and so far I havent been able
to find an answer that works for me.

I have cells which contain both numbers and text and I want to reference
these cells elsewhere, specifically just the numbers. There is no standard
format to the number apart from it is always number followed by text (with no
spaces between).

E.g
123A
45678BD
9CEG

I dont want to split the data out into multiple columns as well and ideally
no macros, is there a function / formula within Excel that will allow me to
just select the numbers?

Thanks

Ed

 

[Mike H]

Try

=LEFT(A1,LEN(LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))))

Mike

 

[edeaston1983]

Thanks Mike - that works perfectly!

Try

=LEFT(A1,LEN(LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))))

Mike