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.

 

[jonk73]

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)

Thank you! I was in pursuit of this formula for over an hour on the web, and it worked perfectly! My data looked like this:
AAC7
AAC8
ADV10
ADV11
ADV20
, the formula pulled in the numbers only.

 

[Rick Rothstein]

Thank you! I was in pursuit of this formula for over an hour on the web, and it worked perfectly! My data looked like this:
AAC7
AAC8
ADV10
ADV11
ADV20
, the formula pulled in the numbers only.

If that is what all your data looks like (the only digits all appearing at the end), then there is a simpler formula to pull out the number...

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

The above formula returns your number as text (in order to preserve leading zeroes, if any), if leading zeroes are not a concern and you want a real number, then use this instead...

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

 

[punktilend]

I hope someone could help me. I am in need of knowing how to extract a certain number from a cell. I would have this in a cell "this number needs to be extracted 12345 but there is a number at the end that does not get extracted 1" How would I extract the specific amount 5 characters long of numbers. Thank you in advance.

 

[Rick Rothstein]

I hope someone could help me. I am in need of knowing how to extract a certain number from a cell. I would have this in a cell "this number needs to be extracted 12345 but there is a number at the end that does not get extracted 1" How would I extract the specific amount 5 characters long of numbers. Thank you in advance.

It would be helpful if you gave us maybe 4 or 5 representative examples of the text you could have in a cell and then show us what you want extracted from them.

 

[punktilend]

Ex. A1: "some words with numbers 12345 but then there are these words and a number 1"
A2:"some other words then numbers 67891 and then there is another number 1"
A3: "more words then the same amount of numbers 12345 which then comes another number 1"

I hope this is an Ok example.

 

[Rick Rothstein]

Ex. A1: "some words with numbers 12345 but then there are these words and a number 1"
A2:"some other words then numbers 67891 and then there is another number 1"
A3: "more words then the same amount of numbers 12345 which then comes another number 1"

If those examples are truly representative and there are no digits located before the 5-digit number you want to retrieve, then this formula should work for you...

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

 

[Lessn]

What if the number being extracted is never in the same place, may be preceded by a date, and may have several other numbers that may or may not be the same length as the number needing to be extracted (number being extracted is always 10 digits)?

Cell A1: Entry Date: 04/23/2015
This is a paragraph of many words and somewhere inside this paragraph is the account number. The account number is 1000123456 and it isn't always in the same place in each paragraph.
Entry Number: 1234567890

Cell A2: Entry Date: 04/22/2015
The account number is 1000123456. This is a paragraph of many words and somewhere inside this paragraph is the account number. It isn't always in the same place in each paragraph
Entry Number: 1234567891

Cell A3: Entry Date: 04/23/2015
This is a paragraph of many words and somewhere inside this paragraph is an account number. It isn't always in the same place in each paragraph. The account number is 1000123456.
Entry Number: 1234567892

 

[István Hirsch]

What if the number being extracted is never in the same place, may be preceded by a date, and may have several other numbers that may or may not be the same length as the number needing to be extracted (number being extracted is always 10 digits)?

Cell A1: Entry Date: 04/23/2015
This is a paragraph of many words and somewhere inside this paragraph is the account number. The account number is 1000123456 and it isn't always in the same place in each paragraph.
Entry Number: 1234567890

Cell A2: Entry Date: 04/22/2015
The account number is 1000123456. This is a paragraph of many words and somewhere inside this paragraph is the account number. It isn't always in the same place in each paragraph
Entry Number: 1234567891

Cell A3: Entry Date: 04/23/2015
This is a paragraph of many words and somewhere inside this paragraph is an account number. It isn't always in the same place in each paragraph. The account number is 1000123456.
Entry Number: 1234567892

It seems possible if the „Entry date:” and „Entry number:” substrings appear in each cell preceding directly the numbers which are NOT to extract. Moreover, if the base text still has more 10-digit numbers, how to identify the one to be extracted.

 

[Lessn]

is there a formula that will help extract this number??????

 

[XOR LX]

Hi.

Perhaps you could confirm whether István's statement re the "Entry date:" and "Entry number:" substrings is true or not?

Regards