Excel formula to extract numbers from text

[Lee733]

I couldn't get these formulas to work:

[TABLE="width: 576"]
<tbody>[TR]
[TD="colspan: 5"]=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&"0123456789"))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]=RIGHT(A6,SUM(LEN(A6)-LEN(SUBSTITUE(A6,{"0","1","2","3","4","5","6","7","8","9"},""))))[/TD]
[/TR]
</tbody>[/TABLE]


Data starting in A4:

[TABLE="width: 296"]
<tbody>[TR]
[TD]Agnes Twp, Fordville SD, Niag Fire - 0104-8[/TD]
[/TR]
[TR]
[TD]Agnes Twp, Fordville SD, Ink Fire - 0104-09[/TD]
[/TR]
[TR]
[TD]Agnes Twp, Larimore SD - 0107[/TD]
[/TR]
</tbody>[/TABLE]


I would like a formula that would return

0104-8
0104-9
0107

Any idea what I am doing wrong?

 

[Zack Barresse]

You're really close. The first two formulas find the position of the first number, but doesn't return the text. For that you need a wrapper function. This will work:

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

Enter formula and copy down. You don't need the other one's.

Edit: by the way, the last formula works for me only on the last one. It doesn't work for all because the substitute function can remove numbers from the test string and potentially decrease it by 1, which is why sometimes it'll be missing a preceding number.

 

[Rick Rothstein]

If the last space character is always immediately in front of the numbers you want to retrieve (as your examples all show), then you can use this simple formula to retrieve them...

=TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",200)),200))

 

[Aladin Akyurek]

In B4 enter and copy down:

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

 

[Lee733]

When I try to use this formula, I get the following error in Excel:

There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

It highlights A4,MIN in the formula.

 

[Lee733]

When I try to use this formula, I get the following error in Excel:

There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

It highlights A1," in the formula.

 

[Rick Rothstein]

When I try to use this formula, I get the following error in Excel:

There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

It highlights A4,MIN in the formula.

I don't know if you tried the formula I posted in Message #3 yet, but your comment above appears to be directed at Aladin's formula... you say it highlights A4,MIN but that is not what Aladin posted for that part of his formula... his formula at that point reads A4,1,MIN... note the ,1 in the middle.

 

[Aladin Akyurek]

Formulas using FIND should work as advertised. Try to copy-and-paste once more. The formula must go in a cell other than A4.

 

[Rick Rothstein]

It highlights A1," in the formula.

Ah, I'll bet your delimiter for formulas is a semi-colon, not a comma, correct? If so, try these...

My Formula (requires the space in front of the number)
------------------------------------------------------------------------
=TRIM(RIGHT(SUBSTITUTE(A4;" ";REPT(" ";200));200))

Aladin's Formula
-----------------------------------------
=REPLACE(A4;1;MIN(FIND({0,1,2,3,4,5,6,7,8,9};A4&"0123456789"))-1;"")

I am not 100% sure of Aladin's formula because the array constant can use both a comma and a semi-colon as part of its syntax in my computer's locale settings, so it is not clear to me what to do with those commas. If what I posted doesn't work for Aladin's formula, then try changing those commas to semi-colons as well.

 

[Lee733]

I tried changing to a semi-colon, and that did not work.