• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to extract only numbers from a cell

M

mohammedkhan

New Member
How to extract numbers from a cell contaning text and numbers

Eg:

Side table wood 100 sar

Corner table 100 sar

Glass table 250 sar

Carpet decorative 200 sar


I need


100

100

250

200


Thanks a lot
 
Best way: Go to Data tab -> Click Text to Columns

click Delimited and check box -> Space - you are done

Remove unwanted columns
 
Hi mohammedkhan


I have entered the data provided by you in range A1:A4.


In the range E1:E10 enter numbers 0,1,2,3,4,5,6,7,8,9.

In the range F1:F26 enter alphabets a,b,c....z.


Enter the formula in B1 = MIN(IFERROR(FIND($E$1:$E$10,A1),100000)) and press Ctrl+Shift+Enter.


In C1 = MIN(IFERROR(FIND($F$1:$F$26,LOWER(A1)),100000)) and press Ctrl+Shift+Enter.


In D1 = TRIM(MID(A1, B1, C1-B1))


I have used the helper columns to better explain the formula. You can combine the formulas if you want.


Amritansh
 
Hi mohammedkhan


Assuming your data is in cells A1:A4 in B1:


=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$25),1)),0),COUNT(1*MID(A1,ROW($1:$25),1)))

This is a array formula and must be committed: CTRL + SHIFT + ENTER

Copy down to B4
 
Good day Kevin@Radstock


I have been looking at your formula and I am stumped! The formula works as it should but if I insert a row above A1 when the cells shift down the number result is limited to two digits, could you spare the time to expalin why this happens I have read and re-read the formula and I am going round in circles.
 
Hi bobhc


When you insert a row above A1 the ROW part of the formula becomes: =ROW($2:$26)

Giving you a array constant:

{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26} instead of:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25}

So if in A1: "Side table wood 100 sar" the array returned would be:

{"i";"d";"e";" ";"t";"a";"b";"l";"e";" ";"w";"o";"o";"d";" ";"1";"0";"0";" ";"s";"a";"r";"";"";""} Then counting along you find that the position of the "1" is "16" instead of "17". The match is returning "16" instead of "17" which is a space in this case!

Hope this makes sense.


Kevin


Note: The numbers have to be together for this formula. as the OP' s data.
 
Does this mean in your formula you are limited to 25 characters in a cell? if so, do I just need to increase the $25 to accommodate a larger character limit?
 
Good day Kevin@Radstock


My thanks for your time and trouble to explain your formula. I will spend some time to understand.

I just thought that as the cells where not absolute that they would auto change their references, time to look at arrays a bit deeper :(
 
@ DaveTurton


That is correct, I only used ROW($1:$25) as the max position of last digit in the OP list was 21. Use ROW($1:$50) if you want to.


@ bobhc


The ROW in the formula is absolute.

As you know, when you insert new rows it moves every thing down, hence the use of the INDIRECT function.


Kevin
 
For the sample data, following should also work:

=SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),99)," sar","")


If you are looking for nice kicking formula then following with Ctrl + Shift + Enter:

=NPV(-0.9,,IFERROR((MID(A1,256-COLUMN(A:IV),1)%),""))

Link to original discussion:

http://www.mrexcel.com/forum/excel-questions/628855-parse-numerics-3.html


Kevin's formula can be made flexible by introducing INDEX inside row function like below:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),0),COUNT(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))
 
Hi Shrivallabha ,


An even older discussion is here :


http://www.pcreview.co.uk/forums/strip-non-numeric-characters-cell-t3787866.html


which was quoted in this topic :


http://chandoo.org/forums/topic/multiple-substitute-extract-numbers-from-alphanumeric


Narayan
 
Hi Narayan,


I missed out on the discussion here on Chandoo.org. I'd posted in the discussion on MrExcel so I remembered it. The best part is, cmsjr has explained the working of formula.
 
You must log in or register to reply here.
Back
Top