How to extract one or two 11 digits numbers from an alpha numeric string

**neo4u44** · 11-16-2011, 07:06 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.

**brokenbiscuits** · 11-16-2011, 07:19 AM

=mid(a1,find(" 03",a1)+1,11)&if(iserror(mid(a1,find(" 03",a1,find(" 03",a1)+1)+1,11)),"",", "&mid(a1,find(" 03",a1,find(" 03",a1)+1)+1,11))

**Colin Legg** · 11-16-2011, 07:28 AM

This works on the examples you provided:

Please Login or Register  to view this content.

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

Thank you very much for your prompt reply.
Now please help me in getting only 11 digits numbers starting with 03.

**Colin Legg** · 11-16-2011, 07:42 AM

I missed the fact that you only want to extract them if they start with 03. Doesn't brokenbiscuits formula do what you want?

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

Dear Colin
you formula returns both numbers. I want only those 11 digits numbers that are starting with 03. Also if there are two 11 digit numbers in a cell starting with 03, I want the result in two different cells.
I hope you can help.
Thanks

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

Dear,
How to get these numbers in two different cells.

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

Yes brokenbiscuit's formula worked. Now the only thing i want to know , if it is possible to get the two 11 digits numbers (if there are any) in two different cells.

**brokenbiscuits** · 11-16-2011, 08:01 AM

=mid(a1,find(" 03",a1)+1,11)
=if(iserror(mid(a1,find(" 03",a1,find(" 03",a1)+1)+1,11)),"",mid(a1,find(" 03",a1,find(" 03",a1)+1)+1,11))

**neo4u44** · 11-16-2011, 08:29 AM

Dear Brokenbiscuits
I am sorry, I didn't get how to use these
should I use both formulas in one cell or should I apply these on the results of earlier formula.

**brokenbiscuits** · 11-16-2011, 08:50 AM

Use the first formula in one cell, and the second in another. The second formula will bring back a secondary number if there is one; if not it will return a blank.

**Haseeb Avarakkan** · 11-16-2011, 12:56 PM

Or,

In B1 & copy DOWN.

=IFERROR(MID(A1,FIND(" 03",A1)+1,11),"")

C1, copy ACROSS & DOWN as much you need.

=IFERROR(MID($A1,FIND(" 03",$A1,FIND(IF(B1="",9E+300,B1),$A1))+1,11),"")

How to extract one or two 11 digits numbers from an alpha numeric string

LinkBack

Thread Tools

Rate This Thread

Display

How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Re: How to extract one or two 11 digits numbers from an alpha numeric string

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions