# Extract 9 digit number from string

1. ## Extract 9 digit number from string

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.

2. ## Re: Extract 9 digit number from string

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)

3. ## Re: Extract 9 digit number from string

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.

4. ## Re: Extract 9 digit number from string

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)

5. ## Re: Extract 9 digit number from string

=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?

6. ## Re: Extract 9 digit number from string

@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.

7. ## Re: Extract 9 digit number from string

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

8. ## Re: Extract 9 digit number from string

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.

9. ## Re: Extract 9 digit number from string

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

very much appreciated

10. ## Re: Extract 9 digit number from string

I have data in cells like this.
A1= SYED AKHTAR ALI SHAH 3630203120065 03009632964

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1