Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To display number in text format in cells | New Users to Excel | |||
Need to display a number when a specific text is entered | Excel Discussion (Misc queries) | |||
How do I display number into form of text or word? | Excel Worksheet Functions | |||
how to display number(10) to text(ten) | Excel Discussion (Misc queries) | |||
In Excel how can a number automatically display in text form | Excel Worksheet Functions |