Extracting Numbers From AlphaNumeric Strings
-
- 2StarLounger
- Posts: 168
- Joined: 25 Apr 2010, 14:12
Extracting Numbers From AlphaNumeric Strings
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
-
- Administrator
- Posts: 7210
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Extracting Numbers From AlphaNumeric Strings
In the absence of others who may know better, and if the format is consistent, you could try something like:jlkirk wrote:Any ideas?
=TRIM(MID(A1,FIND(" ",A1),100))
in B2 and copy down.
Leif
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
This formula seems to do the trick for me:
=MID(TRIM(A1),FIND(" ",TRIM(A1))+1,9)
=MID(TRIM(A1),FIND(" ",TRIM(A1))+1,9)
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
Leif beat me to it with a slightly shorter version...
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
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))
=TRIM(MID(A1,FIND(" ",A1),255))
or to calculate the number of characters needed:
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1)+1))
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 7210
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Extracting Numbers From AlphaNumeric Strings
I did consider that, but worked out from the description that any additional characters should be (unwanted) spaces.HansV wrote: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))
The '100' was just a nominal figure, but unless I'm missing something, only needs to be 50 + 4 + 1 + 4 = 59
Leif
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
Yes, you're correct of course, Leif! Thanks!
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 25 Apr 2010, 14:12
Re: Extracting Numbers From AlphaNumeric Strings
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.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 25 Apr 2010, 14:12
Re: Extracting Numbers From AlphaNumeric Strings
Thanks to all-I apologize and will try and do better in the future! ;-)
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
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)
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)
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
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.
Best wishes,
Hans
Hans
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
Duh, of course.
But I like our complex array formula's better
But I like our complex array formula's better
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting Numbers From AlphaNumeric Strings
It's interesting (and instructive) to see how many different approaches one can use to solve the same problem...
Best wishes,
Hans
Hans