Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[naresh0876]

I tried with ‪300x61‬ with copying the formula as ‪=0+MID(A1,FIND("x",A1)+1,99) expecting 61 should be displayed.

I tried using Evaluate formula at the end i got 0+"61" which resulted #value!

 

[XOR LX]

Can you tell me what precisely is in A1? Is it "300x61"? Looks like you may have some additional spacing character at the end. What is the result of the formula:

=CODE(RIGHT(A1))

?

Regards

 

[naresh0876]

I got 63

 

[Excellis]

Hi Ron.
This is an awesome formula that I have used over and over again since I first discovered it on this site some time back. I can't say I understand it but it's always worked and apart from occasionally having issues with the first row (R4) it's served me well.
Up to now I have only ever used it on a sheet with a maximum of 150 rows.
I now need to use it on a worksheet with around 500 rows. It seems to work down to row 304 and then I get this "#NUM!"
Do you know why this might be? Nothing has changed between rows 304 and 305
The cell contents I'm trying to extract from are something like this:

(R304) shop3567@myarea.co.uk
(R305) shop3828@myarea.co.uk


Many thanks in anticipation
Tom

I played around with the formula some more...
This non-array formula seems to be working:

B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

</SPAN></SPAN>

 

[Rick Rothstein]

I played around with the formula some more...
This non-array formula seems to be working:

B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*[B][COLOR="#FF0000"]10^ROW($1:$25)[/COLOR][/B]/10)

Hi Ron.
This is an awesome formula that I have used over and over again since I first discovered it on this site some time back. I can't say I understand it but it's always worked and apart from occasionally having issues with the first row (R4) it's served me well.
Up to now I have only ever used it on a sheet with a maximum of 150 rows.
I now need to use it on a worksheet with around 500 rows. It seems to work down to row 304 and then I get this "#NUM!"
Do you know why this might be? Nothing has changed between rows 304 and 305
The cell contents I'm trying to extract from are something like this:

(R304) shop3567@myarea.co.uk
(R305) shop3828@myarea.co.uk

The problem occurs in Ron's formula at the point I marked in red above. The
Largest allowed positive number in Excel is 9.99999999999999E+307. The red highlighted text above is evaluates to 10^305, which in scientific notation is equivalent to the E+305 part of the returned value, but to make a full number, you must put the retrieved digits in front of it... 3828E+305 which in reduced scientific notation is 3.828E308 which is larger than the largest allowed number. As long as the row number is 304 or less for your 4-digit numbers, it works, but crossing into row 305 makes the resulting number too large for Excel to handle.

Now, to your problem... is the number you want to retrieve always the only number before the @ sign in your text? If so, this should work for you...

=-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

 

[Excellis]

Good morning, Rick.
That is absolutely awesome. Works a treat.
Thank you so so much. Far better than my workaround which involved splitting the table, copying and pasting as values and recombining.


Kindest Regards
Tom

 

[kbend3r]

Hi Rick,

Do I need something this complex if what I am needing to do is simply extract the numbers from a text string?

Example:
A1: They went to the park 63 times
A2: He went to the park 440 times
A3: Bob and Dawn went to the park 9220 times

What I want extracted is the integer in number format:
B1: 63
B2: 440
B3: 9220

Thank you so much for your consideration.

Kind Regards,
Kim

 

[Rick Rothstein]

Hi Rick,

Do I need something this complex if what I am needing to do is simply extract the numbers from a text string?

There were 76 message posted prior to the message with your question in it... exactly what do you mean by "this complex" (what are you referring to)? Basically, I think we will need to know if you want a formula or VBA code and whether that number in the text will always be the only number in the text.

 

[kbend3r]

There were 76 message posted prior to the message with your question in it... exactly what do you mean by "this complex" (what are you referring to)? Basically, I think we will need to know if you want a formula or VBA code and whether that number in the text will always be the only number in the text.

My sincerest apologies. I just checked back with my manager and he provided me with some information that would have been nice to have yesterday. The number is always at the end of the contents in the cell and it is always 7 characters long so a simple RIGHT() command will resolve his request.

While I no longer am in NEED of my original request, I would still like to learn. Based on my previous understanding of the request, yes, the number will always be the only number in the text. It will be of variable length and will start at a variable location. It may or may not be at the end of the contents in the cell.

Thank you for your help on this and now that it is no longer "important" please respond at your convenience.

Kind Regards,
Kim

 

[Rick Rothstein]

While I no longer am in NEED of my original request, I would still like to learn. Based on my previous understanding of the request, yes, the number will always be the only number in the text. It will be of variable length and will start at a variable location. It may or may not be at the end of the contents in the cell.

Here is one way to do it...

=-LOOKUP(0,-LEFT(MID(E5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E5&"0123456789")),15),ROW(INDEX(A:A,1):INDEX(A:A,15))))