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

 

[Pecoflyer]

Hi,

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

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

E.g
123A
45678BD
9CEG

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

Thanks

Ed

Hi, have a look at this site'Strings and Manipulations
(http://www.mvps.org/dmcritchie/excel/strings.htm) under the "Relate
Topics" paragrap

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[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