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.

 

[Rick Rothstein]

1) Yes, the comma is decimal point
2) Yes, original formula works in most cases, but if string is "0,5aa" the result is "0", not "0,5"

Do not use my formula... it appears to not always work correctly.

 

[BatTodor]

Do not use my formula... it appears to not always work correctly.

For now I found just this bug - with "0,5aa"

Best regards
Todor

 

[Rick Rothstein]

For now I found just this bug - with "0,5aa"

It fails with something like this as well...

10 Parking

 

[Kingkoopa]

Hey guys happen to found this thread on google.

Is there a way to extract the following numbers from the following text string :

12Apple/50Grams	50
12Apple/17.5Grams	17.5
23Pears/22Grams	22
15Watermelons/15.2Grams	15.2

<colgroup><col style="mso-width-source:userset;mso-width-alt:6546;width:134pt" width="179"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Right column is the result I am looking for.

 

[Ron Coderre]

With your sample data in A1:A4

This formula, copied down, returns the number between the "/" and "Grams"

B1: =IFERROR(--MID(LEFT(A1,SEARCH("grams",A1)-1),FIND("/",A1)+1,15),0)

Is that something you can work with?

 

[Rick Rothstein]

With your sample data in A1:A4

This formula, copied down, returns the number between the "/" and "Grams"

B1: =IFERROR(--MID(LEFT(A1,SEARCH("grams",A1)-1),FIND("/",A1)+1,15),0)

Is that something you can work with?

Assuming the quantity will always be in grams as you did, I would suggest you change the search word from "grams" to "gram" just in case the value after the slash is "1gram".

 

[Kingkoopa]

With your sample data in A1:A4

This formula, copied down, returns the number between the "/" and "Grams"

B1: =IFERROR(--MID(LEFT(A1,SEARCH("grams",A1)-1),FIND("/",A1)+1,15),0)

Is that something you can work with?

Hi works perfectly thanks! What does the -- infront of the mid means though?

 

[Rick Rothstein]

Hi works perfectly thanks! What does the -- infront of the mid means though?

It converts a text number to a real number (it is like multiplying by minus one twice... to negatives make a positive). You could also do it by adding zero or multiplying by 1.

 

[Zixik]

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)

Thanks so much for this - it works perfectly. What does the ROW($1:$25) do?

 

[DocAElstein]

Thanks so much for this - it works perfectly. What does the ROW($1:$25) do?

See here for general info
ROW function explained | Get Digital Help - Microsoft Excel resource

And here for in context to Ron’s formula
http://www.mrexcel.com/forum/excel-...act-only-numbers-text-string.html#post2194227

_.........................

In the very simplest terms, it produces an Array, “ vertical “ 1 Dimension, of consecutive indices....
{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}

_ It is sort of defining here how long the String is that you are wanting to work on / how many “things are compared pair wise” as it were.

_............................

To Demo:

_1) Type that formula =ROW($1:$25) in any cell in a spare sheet.
It will return just 1. That is because although all 25 values are there, you are only giving one cell for Excel to paste out all values to. So it sort of truncates the list only giving you the first value.
After typing that formula in any cell, click on the cell, then click on the Formula bar, select ( highlight ) that formula , then hit F9. - This does an instant Evaluation and so should reveal
{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}
( make sure you now hit Esc to get back to the Formula now )

Or

_2) Run this code on a spare Workbook ( for simplicity put the code in a normal macro and select a spare Worksheet. It will then work on that ActiveSheet

[color=blue]Sub[/color] RowStuff()
Range("A20").Formula = "=ROW($1:$25)"
Range("B20:B44").FormulaArray = "=ROW($1:$25)"
[color=blue]End[/color] [color=blue]Sub[/color]

The first line just does what you did manually to put the Formula in a cell
The second line does the “CSE thing”, which in this case is just telling Excel to put the Values in 25 cells, hence allowing you to see all values.

_..................................


This Evaluate Row ( or Column ) stuff is very powerful and is frequently used to get an Array of sequentially listed indices :

http://www.mrexcel.com/forum/excel-...tions-evaluate-range-vlookup.html#post3944863
https://usefulgyaan.wordpress.com/2...y-without-loop-application-index/#comment-511

( or similar ! )
http://www.mrexcel.com/forum/excel-...-1-dimensional-single-column.html#post4370502


_....

Specifically what is happening in Ron’s Formula is a bit beyond me. But basically if you work through sections of it doing the “click on the Formula bar, select ( highlight ) that formula then hit F9” bit, then that will reveal what is going on..