Extracting Multiple Numbers from String

[bolillo1234]

I have thousands of fields that contain addresses and I need to extract only the numbers. The problem I am facing is that there are two numbers within the string and they begin at different times.

Examples of the data
1409 N 250 W
259 West 158 Johnson Blvd
109829 South 344 East

What Im trying to get
1409250
259158
109829344

Thanks,
Bolillo

 

[Jazzed]

Hi I’m a Newbie here to the board. I tried the code above and it works great. I have a little variation to the above string and my skills are limited in figuring it out. An example of my string is... in Cell G1, “C2_PU07_15.04.2013_633759 John Henry” the number I need is after the _ “633759” I currently use text to columns to break it out but I know there is an easier way. Thanks for your consideration.

It looks like you're only interested in returning "633759". If this is correct, assuming that A2 contains the text string...

1) If the number always occurs immediately after the last "_" and always consists of six digits:

=MID(A2,FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))+1,6)

2) If the number always occurs between the last "_" and before the first " ", with the number of digits varying:

=MID(A2,FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))+1,FIND(" ",A2)-FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1)

Hope this helps!

 

[Rick Rothstein]

It looks like you're only interested in returning "633759". If this is correct, assuming that A2 contains the text string...

2) If the number always occurs between the last "_" and before the first " ", with the number of digits varying:

=MID(A2,FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))+1,FIND(" ",A2)-FIND("^^",SUBSTITUTE(A2,"_","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))))-1)

Another shorter way (less function calls) to do it...

Volatile, but safe against row inserts or deletes...

=LOOKUP(9E+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"_",REPT(" ",99)),99)),ROW(INDIRECT("1:99"))))

Non-volatile, but sensitive to row inserts and deletes...

=LOOKUP(E8+307,--LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"_",REPT(" ",99)),99)),ROW(1:99)))

 

[abbotti]

You guys are great. I went went with the lookup beacuse the number of digits varies but i can ues the mid for some other situations. Thanks!

 

[earthworm]

And here is another array-entered formula that has been posted in the past by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

Can you please explain the logic of this formula piece by piece i will be grateful


=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

I Have understood the green part please explain the red part piece by piece

secondly what is the function of this formula in the mid function I have seen the logic for this

ROW($1:$300),

=MID(A1,row(indirect(1&":"&len(A1)),1)

You have used the logic of row and without indirect . what is the difference behind it . I want to understand this . please explain me . Please exmplain in simple language piece by piece so that i can use the formula applying the logic instead of remembering your formula

 

[earthworm]

Bump !!

 

[dhaile]

I'm new to MrExcel, but I've found the information very useful so far. However, I have a question related to the post below. Is there a way to utilize this function or a similar function witha string longer than 14 characters? For example, I'd like to extract only the numerals from the following strings:

2242341090HE171165

2243392880171 &165

2297244910LA172/165

2301529440TA171

<colgroup><col></colgroup><tbody>
</tbody>

Tks. dhaile

==============================================================================

And here is another array-entered formula that has been posted in the past by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

 

[Rick Rothstein]

I'm new to MrExcel, but I've found the information very useful so far. However, I have a question related to the post below. Is there a way to utilize this function or a similar function witha string longer than 14 characters? For example, I'd like to extract only the numerals from the following strings:

2242341090HE171165

2243392880171 &165

2297244910LA172/165

2301529440TA171

<tbody>
</tbody>

You could split the text into two parts and concatenate the results together. Remember, this is an array-entered** formula...

=MID(SUMPRODUCT(--MID("01"&LEFT(A1,14),SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&LEFT(A1,14),ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)&MID(SUMPRODUCT(--MID("01"&MID(A1,15,99),SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&MID(A1,15,99),ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Pyrrhus272BC]

Here's a formula approach...

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

...where A2 contains the text string of interest. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Domenic,

I am attempting to decipher this formula myself (to reverse engineer for my needs), however, I am not able to ascertain some elements. What would I do, if I want it to kick out a result of the first numbers it encounters?

 

[Domenic]

Can you please provide us with a sample of the data, along with the expected result?

 

[István Hirsch]

Deleted as not relevant.