Formula to display just number and not text
 

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

Formula to display just number and not text
 

edeaston1983;282649 Wrote:
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

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


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=78953

Formula to display just number and not text
 

Try

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

Mike

"edeaston1983" wrote:

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

Formula to display just number and not text
 

Thanks Mike - that works perfectly!

"Mike H" wrote:

Try

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

Mike

"edeaston1983" wrote:

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