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

 

[themayhall]

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!

I realize this is seven years after-the-fact, but I use this formula all of the time, and wanted to stop and say thanks.

 

[XOR LX]

I realize this is seven years after-the-fact, but I use this formula all of the time, and wanted to stop and say thanks.

It is nice, though perhaps seems a little 'clunky' now in light of:

=NPV(-0.9,IFERROR(0+MID(A2,1+LEN(A2)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)/10,""))

Regards

 

[themayhall]

It is nice, though perhaps seems a little 'clunky' now in light of:

=NPV(-0.9,IFERROR(0+MID(A2,1+LEN(A2)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)/10,""))

Regards

Thanks XOR LX,
I'll give this one a spin as well on my next reporting cycle.
Thanks!

 

[FlameRetired]

@ XOR LX

I am confused regarding a detail of this most amazing formula. It pertains to the nested IFERROR.

According to the online help file for NPV:

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values [emphasis added] in the array or reference are ignored.

I tried experimenting with this formula (removing the IFERROR) and while NPV will ignore FALSE it clearly does not ignore errors. What am I missing?

 

[XOR LX]

@FlameRetired

Very interesting. I never bothered to read the online help for that function, but I completely agree with you.

Not only are error values not accepted in any form (i.e as part of a worksheet range, as an argument or as an element of an array passed as an argument), it appears that there are further inaccuracies in the statements given there for this function.

Text for example, is allowed as part of a worksheet range or as an element of an array passed as an argument, but not as an argument. So, for example:

=NPV(-0.9,1,{2,"X",3},4)

is fine, which we would expect from:

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

but:

=NPV(-0.9,1,2,"X",3,4)

is not, which would appear to contradict:

Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.

I searched briefly but could find no cases of these anomalies having previously been reported. Perhaps someone here will have an explanation. Failing that, I might raise it officially with Microsoft.

Regards

 

[FlameRetired]

Thank you for looking at this and responding.

I had not investigated the text variations (array and argument) you listed. That is interesting and yes another apparent contradiction.

Still that is a very cool formula.

I seem to recall you posting it before on your web site. I don't recall specifically which thread, but thank you for posting it again. It's in my "to keep" file now.

 

[kevinT91]

Partial noob here, so please have mercy... I sort of get the concepts there, but I am still lost. I found the thread very informative, but am still at a loss as to how I can deal with another *****ly problem of my own... I have patient data coming in big chunks, so as not to manually select the data from the provided patient files, and want to create a function, or something similar, to deal with something. That something is the total sum of lymph nodes in their pathology reports which were extracted along the resected rectum, as well as the total sum of positive lymph nodes out of those. A text sample would go something like this (I'm translating it, since it's originally in Romanian, but the wording is very similar, as I'm counting on the word order and proximity to be of help in this):

18286 - 1 lymph node with metastasis
10864 - blah blah only text blah. 8 of 12 lymph nodes with/present metastasis.
18754 - 1 of 5 lymph nodes present micrometastasis
17648 - text blah. 3 lymph nodes without/ don't present metastasis
19864 - lymph node without metastasis

How can I get the program to count them all and in their own proper categories? I'm guessing I need to sum the numbers (how can I get the program to understand that those are numbers, not just characters?) before an "of" with those before a "lymphnode(s)" that is followed somewhere in the same sentence by a "with"/"present", yet without a "don't", and then the other remaining numbers together... How in the world can I do that?! I hope it's possible... I'm not even sure if it is ... XD

 

[MARK858]

Duplicate of the post here

 

[kevinT91]

True. I was hoping to get a quicker answer. Sorry if I did something wrong. I'm pretty new to posting on such forums. I'm not posting it on any other thread, just these 2.

 

[Rick Rothstein]

True. I was hoping to get a quicker answer. Sorry if I did something wrong. I'm pretty new to posting on such forums. I'm not posting it on any other thread, just these 2.

Please see: A message to forum cross posters

The article at the above link was written with people who cross-post to multiple forums, but the advice also applies to cross-posting within the same forum as you did.