Extract 9 digit number from string

**Jbentley** · 10-28-2010, 03:27 AM

Hi,
I'm trying to find a better (smarter) way of extracting a 9 digit number from a string.
The number can start anywhere within the string, and the string may contain other numbers (though not immediately adjacent to the 9 digit number).

I wrote a clumsy macro to text each character (from left to right) to see if it is a number (using a 'if variable >=chr(48) and variable <=chr(57) test) and then tested each of the following 8 characters. Once I established that I had a group of 9 numbers in the string then put the 9 numbers into an adjacent cell.
Then looped through the column with the strings.

I'm thinking there is a better way to do this and any suggestions / pointers most appreciated.

**Bob Phillips** · 10-28-2010, 03:49 AM

Try this array formula

=MID(A2,MIN(IF(ISERROR(1*(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A2))))),9)

**beaunydal** · 10-28-2010, 04:10 AM

When A1 is "kjsdhf 6565 kjdfh 465850874hgfj4644" this array formula (must be entered using Ctrl+Shift+Enter key combination) returns 465850874...

Please Login or Register  to view this content.

Bob, I tried your formula on the same string and it returns 6565 kjdf.

**DonkeyOte** · 10-28-2010, 04:21 AM

Perhaps

Please Login or Register  to view this content.

The above will return 0 if no MATCH is found [also returns first 9 digit value should more than 1 exist in the string]
(given use of INDEX it is non-Volatile)

**Marcol** · 10-28-2010, 05:11 AM

Adding to DonkeyOtes' solution,

=MID(SUBSTITUTE(A13," ","")&"000000000",MATCH(TRUE,INDEX(ISNUMBER(MID(SUBSTITUTE(A13," ","")&"000000000",ROW(A$1:INDEX(A:A,LEN(SUBSTITUTE(A13," ",""))+1)),9)+0),0),0),9)+0

This will return 465850874
from
kjsdhf 6565 kjdfh 465850874hgfj4644

Is it possible that a nine digit number could be preceded by a larger string of numbers?

**DonkeyOte** · 10-28-2010, 05:28 AM

@Marcol, I could be wrong of course but I don't think you need more than one SUBSTITUTE as long as you replace the blanks with a non-numeric character.
I did modify my suggestion along these lines earlier

However, the point re: # exceeding 9 digits in length preceding the nine digit # is valid for all suggestions made thus far.

**Marcol** · 10-28-2010, 05:36 AM

Apologies Don, didn't see your edit to post #4

**Marcol** · 10-28-2010, 06:53 AM

If you have a nine digit number preceded by a larger string of numbers, this UDF might be of use.

Please Login or Register  to view this content.

Enter as e.g.

Please Login or Register  to view this content.

By changing the second arguement you can search for the first string of digits that match that length exactly.

**Jbentley** · 10-29-2010, 03:31 AM

Thanks All !!!
I'll give these a whirl.

very much appreciated

**neo4u44** · 11-16-2011, 07:02 AM

I have data in cells like this.
A1= SYED AKHTAR ALI SHAH 3630203120065 03009632964
A2= Nisar Ahmad Nadeem 03006884081
A3= KHADIM HUSSAIN 3630309830551 03027369268

Now every cell has text and two or three numbers separated by uneven spaces.
I want to
1. extract the last 11 digit number from each cell i.e the one starts with 03
2. If in a cell there are two 11 digits numbers starting with 03, I want to extract them both either in separate cells or in one cell but with comma separator.

I am an average user so unable to capitalize on the previous discussion by you guys.
Thanking you in anticipation.