Extracting Numbers From AlphaNumeric Strings

[jlkirk]

I have also posted this on Woody's. Problem: In cell A1-A100, I have the following types of data: "ABC 1234-5678", or "DEF 9012-3456 ", etc. In other words, there is text entered as the first, say, 1-25 characters of a cell, then anywhere from 1 to 50 spaces, then 4 numbers, then a hyphen, then 4 more numbers, then anywhere from 0 to 50 additional spaces. What I would like to do is extract from cells A1:A100 all of the numbers in their original format (i. e., including the hyphen, "1234-5678", "9012-3456") into cells B1:B100. Any ideas? Thanks in advance

[Leif]

Any ideas?

In the absence of others who may know better, and if the format is consistent, you could try something like:
=TRIM(MID(A1,FIND(" ",A1),100))
in B2 and copy down.

[Jan Karel Pieterse]

This formula seems to do the trick for me:

=MID(TRIM(A1),FIND(" ",TRIM(A1))+1,9)

[Jan Karel Pieterse]

Leif beat me to it with a slightly shorter version...

[HansV]

See Leif's reply below - the modification I proposed isn't necessary.

Leif's formula should work well, but since you mention that there could be up to 50 spaces before and after the digits, it's safer to increase the last argument of the MID function:

=TRIM(MID(A1,FIND(" ",A1),255))

or to calculate the number of characters needed:

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1)+1))

[Leif]

Leif's formula should work well, but since you mention that there could be up to 50 spaces before and after the digits, it's safer to increase the last argument of the MID function:

=TRIM(MID(A1,FIND(" ",A1),255))

or to calculate the number of characters needed:

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1)+1))

I did consider that, but worked out from the description that any additional characters should be (unwanted) spaces.
The '100' was just a nominal figure, but unless I'm missing something, only needs to be 50 + 4 + 1 + 4 = 59

[HansV]

Yes, you're correct of course, Leif! Thanks!

[jlkirk]

I tried all, and they seemed to work fine. However, some of the text might be more than one word, with spaces in between, or they might be separated by a blank then a "/" then another blank. This causes some issues! It appears the formulas pick up right after the first space between words.

[HansV]

It helps if you provide all relevant information at the outset, instead of revealing it gradually.
Try this array formula (confirm with Ctrl+Shift+Enter):

=TRIM(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$100),1)),0),255))

It won't work if the text at the beginning contains digits too.

[jlkirk]

Thanks to all-I apologize and will try and do better in the future! ;-)

[Jan Karel Pieterse]

Well, we did follow your description to the letter, didn't we!
Assuming there are NO digits before the ones you want to extract, try this array formula:

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

[HansV]

If you always need the last 9 non-blank characters, Gfamily's suggestion in the Windows Secrets Lounge is much simpler. If the number of digits can vary, however, that won't work.

[Jan Karel Pieterse]

Duh, of course.
But I like our complex array formula's better

[HansV]

It's interesting (and instructive) to see how many different approaches one can use to solve the same problem...