Given an alphanumeric string, one may want to perform the following

Extract phone number from the string

Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string.

One may want to extract only the phone numbers to another column.

You may refer to the my solution in this workbook.

Extract one specific 20 digit number from the string

Assume cell descriptions which contains two 20 digit numbers occurring anywhere in the string.  Once may want to extract only that 20 digit number which has the word New before it.

You may refer to my solution in this workbook.

• I have been surfing online more than 3 hours today, yet I never found any interesting article like yours Extract phone number from address string | Ashish Mathur's Blog. It’s pretty worth enough for me. Personally, if all website owners and bloggers made good content as you did, the web will be much more useful than ever before.

• Manik says:

How can i remove the zeros only before numeric (Zeros contains after numeric also) values only

Inv No
TLH -000000000000002/0000000375
CTK -000000000000008/0000000391
EXP -000000000000033/0000000933
JLN -000000000000008/0000000152
CTK -000000000000004/0000000197
ANS -000000000000005/0000000239
CPR -000000000000001/0000000242
BWDH-000000000000007/0000000253
BWDH-000000000000008/0000000254
BWDH-000000000000010/0000000293
ANS -000000000000006/0000000298
SMB -000000000000003/0000000330

Desired Result required is

Inv No
TLH -2/375
CTK -8/391
EXP -33/933
JLN -8/152
CTK -4/197
ANS -5/239
CPR -1/242
BWDH-7/253
BWDH-8/254
BWDH-10/293
ANS -6/298
SMB -3/330

Best Regards
Manik Nag

• Hi,

Assuming the first entry is in cell B3, enter this formula in cell C3 and copy down

=LEFT(B3,SEARCH("-",B3))&1*MID(B3,SEARCH("-",B3)+1,SEARCH("/",B3)-SEARCH("-",B3)-1)&"/"&1*RIGHT(B3,LEN(B3)-SEARCH("/",B3))

Hope this helps.

• Manik says:

Howdy Ashish,
Your knowledge is most extraordinary collection of your talent, and your implementation of your knowledge is so unique which are uncomplicated and most helpful at critical times. My gratitude is beyond my words and god may bless you.
Regards
Manik

• I need a formula that will isolate just the numbers from a cell of alpha/numeric data of food distributor case sizes in a large excel file of 700 + items like the few examples below:

In Cell A1 through A4 is:

A. 2/2LB
B. 12/10OZ
C. 3/12 COUNT
D. 24/16.9 OZ

The data almost exclusively comes with a numeric portion first of how many are packed in a case, then a ”/” to separate the size (i.e. 12 LB, 15 OZ, 12 COUNT etc..). Sometimes there is no space between the second set of numeric data and the last alpha like OZ or LB.

I then need to get the numbers that have been isolated to be multiplied to come up with a total numeric unit measure number per case from that data that has been isolated

(using above as example) – RESULT TO BE

A. 4 (2X2)
B. 120 (12X10)
C. 36 (3X12)
D. 405.6 (24X16.9)

This all needs to be calculated in new cells like B1, C1 or D1 etc…

• Mary says:

Hi,

I am using an article you wrote for guidance with a project and I am wondering if you could help explain a section of it. The article is here: http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx. I am using Excel 2010.

The problem I have is basically the same as what you explain in your article. I have a cell (C326) with text that looks like this: ”Comp Any (Place) 17463 Phase II.” And I am trying to extract just ”17463” from it. The numbers I want to extract are not always the same number of digits and not always at the same location in the string.

Step 1 of your formula, breaking the string into separate characters, doesn’t seem to work for me. When I input =MID(C326,ROW(\$1:\$9),1), it only returns the first character of the alphanumeric string, (”C” in this case). As a result, when I add the ISANUMBER function, it returns ”FALSE,” because ”C” is not a number. However, I would like it to return ”TRUE” because the cell does contain a number. (The first number would be a ”1” in this example). Do you know what I might be doing wrong? Could you please explain to me the purpose of adding ”ROW(\$1:\$9)” to the MID function? You say that this puts the characters–virtually–into different rows of the worksheet. But why do you choose rows 1 through 9? And how does it do this virtually?

Thank you so so much for your help!

• Hi,

What result would you want if there are other numbers appearing in the string as well. So if the string in cell A4 is “Comp Any (Place) 3421 Area A 3489123 Phase II”, then what result do you want – 3421 or 3489123?

Please give a representative list of your alphanumeric strings so that I can frame a formula.

• Mary says:

The numbers that I am trying to extract are either 5 or 8 digits. In the case where it is 8 digits, it will always start with “000.” It is OK with me if it only returns the 5 digits to the right of the zero. In the case where the number is 5 digits, it could start with any number. Also, there is not a number in every single string. If there is not a number, I would like it to return nothing. I’m not sure if it is possible to do this given the variety in my string… Here is a representative list with the results I would want it to return:

Comp Any (Place) 17439 Phase II : 17439
Drinks Food 00087654 : 00087654 or 87654 (either is fine)
ABCD – 74653 – Project : 74653
Name (Place) Burger King : (return nothing, because no number)
Company 34567 start December 1999 : 34567
MAN-AI Country (Place) 14-15 January 1998-34643-Foot : 34643
MAN-AI Country (Place) 14-15 January 1998-00044643-Foot : 00044643 or 44643

Thank you so much for your help!

• Hi.

Suppose your alphanumeric strings are in range A2:A8. In cell B8, enter this array formula (Ctrl+Shift+Enter) and copy down

=IF(ISNA(LOOKUP(1E+100,1*MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),5))),””,MAX(IF(ISERROR(1*(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),5))),””,1*(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),5)))))

Hope this helps.

• bhupender says:

• Hi,

Try this

1. Suppose the string is in cell A5
2. In cell B5, enter this formula

=MID(SUMPRODUCT(–MID(“01″&A5,SMALL((ROW(\$A\$1:\$A\$25)-1)*ISNUMBER(-MID(“01″&A5,ROW(\$A\$1:\$A\$25),1)),ROW(\$A\$1:\$A\$25))+1,1),10^(25-ROW(\$A\$1:\$A\$25))),2,25)

Hope this helps.

• Arjun Singh says:

Hi Ashish,

I need your help with regards to an issue, where i am not able to find a (text or number) from a alpha numeric column. As an instance, i need to find 372900 and in a column of alpha numeric texts which includes
PAG X CASSA SDO FT 370709 DEL 27/12/11
PAG X CASSA SDO FT 8983 09/01/14
PAG X CASSA SDO FT 370709 DEL 27/12/11

So how to find the correct one from this column and similarly we do have other numbers to find. So Please do let me know if you have a solution for this issue. I am unable to upload my excel file else i would share it with you.

Thanks & regards Arjun Singh

• Hi,

In cell B2, enter this formula and copy down

=ISNUMBER(SEARCH(“372900”,A2))

Hope this helps.

• Arjun Singh says:

Hi Ashish,

No its only working for a particular cell,

I want it to work like a lookup, because in one column i have a reference like 372900, 8983 and 370709 and in another column we have

PAG X CASSA SDO FT 370709 DEL 27/12/11
PAG X CASSA SDO FT 8983 09/01/14
PAG X CASSA SDO FT 370709 DEL 27/12/11

and it is in thousands so how can i find the unique value.

For the same reference i share a file with you in linkedin to where i occurred an issue.

• Hi,

Try this formula in cell B2 and copy down

=SUMPRODUCT(1*(ISNUMBER(SEARCH(\$E\$2:\$E\$4,A2))))

E2:E4 has 372900,8983,370709.

Hope this helps.

• Arjun Singh says:

Hi,

It’s working fine but how to get a number rather than true false.

• Arjun Singh says:

No, Unfortunately it give me either 0 or 1.

• Arjun Singh says:

Sorry for inconvenience,

I just want to search a whole number “370709”, from below given alphanumeric text[E2:E4]. but the search will not be from a particular cell, it will be from a whole column.

PAG X CASSA SDO FT 370709 DEL 27/12/11
PAG X CASSA SDO FT 8983 09/01/14
PAG X CASSA SDO FT 370709 DEL 27/12/11

• KVSNHL Sastry says:

Hi,

I am having a cell (A1) with data

A1. T-12.321 M – 3.124 B -0.03

(the spaces and hyphen are not exact in all cell, as shown above.)

Now I would like to have

A2. 12.321
A3. 3.124
A4. 0.030

• KVSNHL Sastry says:

Yes.
I made a small code.. but it is for a single cell.. there are near about 3500+ cells.
It would be great, if you can add some code to the following for a group of selected cells.. like 30 or 40 at a time. The are all in a single row.. like A2, B2, C2….

Sub NewData()
ActiveCell.Select
ActiveCell.Offset(1, 0).Range(“A1”).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

ActiveCell.FormulaR1C1 = “=TRIM(CLEAN(SUBSTITUTE(R[-1]C,”” “”,””””)))”
ActiveCell.Offset(1, 0).Range(“A1”).Activate
ActiveCell.FormulaR1C1 = “=TRIM(CLEAN(SUBSTITUTE(R[-1]C,””-“”,””:””)))”

ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “=SUBSTITUTE(R[-1]C,””M:””,”” M:””)”

ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “=SUBSTITUTE(R[-1]C,””B:””,”” B:””)”
ActiveCell.Select
Selection.Copy

ActiveCell.Offset(-4, 0).Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Columns(“A:A”).EntireColumn.EntireColumn.AutoFit

ActiveCell.Offset(5, 0).Range(“A1”).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

ActiveCell.FormulaR1C1 = _
“=MID(R[-5]C,FIND(“”T:””,R[-5]C)+2,FIND(“”M””,R[-5]C)-(FIND(“”T:””,R[-5]C)+2))”
ActiveCell.Select
Selection.NumberFormat = “0.000”
Selection.Value = Selection.Value + 0

ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
“=MID(R[-6]C,FIND(“”M:””,R[-6]C)+2,FIND(“”B””,R[-6]C)-(FIND(“”M:””,R[-6]C)+2))”
ActiveCell.Select
Selection.NumberFormat = “0.000”
Selection.Value = Selection.Value + 0

ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
“=MID(R[-7]C,FIND(“”B:””,R[-7]C)+2,FIND(“”B””,R[-7]C))”
ActiveCell.Select
Selection.NumberFormat = “0.000”
Selection.Value = Selection.Value + 0

ActiveCell.Select
ActiveCell.Offset(-6, 0).Range(“A1”).Select
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Selection.EntireRow.Delete
End Sub

• dritan gashi says:

hi, how can i extract numbers from codes like this CRLSVT73T29D862H
DFZNTN50M17B180E
LFNRMN36M07G187A
MRTNNA64A64B180X
SMRLRD74H12B180E
CRVGCR75L04B180B
CHMRMN71D53B180V
DNSGPP50M14F376B
the problem is that the column has 50000 rows, the alphanumeric codes are in column .
thank you

• Hi,

Suppose your data is in range A2:A9. In cell B2, enter this array formula (Ctrl+Shift+Enter) and copy down

=MID(SUMPRODUCT(–MID(“01″&A2,SMALL((ROW(\$A\$1:\$A\$25)-1)*ISNUMBER(-MID(“01″&A2,ROW(\$A\$1:\$A\$25),1)),ROW(\$A\$1:\$A\$25))+1,1),10^(25-ROW(\$A\$1:\$A\$25))),2,25)

Hope this helps.

• Srinath says:

HI Ashish,

Great Blog with very simple solutions.

I had a question. If there are 2 Cell numbers, can we have a formula to extract the second mobile number into another column

• Hi,

Thank you. Please share some data and also show your expected result.

Hi Ashish,

Your mobile number search in a particular sentence is the perfect thing which I was looking for. But in my case, I am looking for a particular set of words which I have to be extracted from the sentnecs, as in your case how the mobile no is extracted, I have a particular set of words which are to be extracted . Can you please assist me on the same. Let me know if any more details are required

• Hi,

Hi,

Unfortunately I do not have a workbook link which I can share but I can show u the example as below..

Eg: 1)
Cell B2 contains the below sentence
Ratings Obtained for FS-ALL-1 pertaining to interest of client
Expected result in c2
FS-ALL-1

Eg 2)
Cell B2 contains below sentence
Controls regarding telephone recording and Voice reconciliation
Expected Result in C3
Telephone Recording

Eg: 3)
Cell B4 contains below sentence
Ratings Obtained for FS-SAN-1 pertaining to documents verification
Expected Result in C3
FS-SAN-1

So, the expected result is a set of words which I have readily available and not a random word which I want to choose from the sentence. I just need to extract them from the sentence. So I am looking for a formula which can extract only words from a sentence. There are some 20-30 sentence from which I need to extract these specific set of words

• Hi,

In cell C2, enter this formula and copy down

=LOOKUP(2,1/SEARCH(\$F\$2:\$F\$4,B2),\$F\$2:\$F\$4)

F2:F4 has your list of keywords.

Thanks a ton Ashish. It worked the way I wanted. You are awesome.

Just two questions I have:

1) I tried to break down and understand, but couldn’t understand what is “2,1/”. Can you please explain

2) As I had a readymade list of words it was easy to find the words from the range given. What if I do not have the list of words and I want to find a random word from the same given sentence. Is there a formula for same. You can use ant sentence as eg from the earlier query.

Thanks,

• You are welcome. The SEARCH() function returns either a number or an error. When the result of the SEARCH() function is divided by 1, the result will be an error or a number less than 1. The LOOKUP() function has a property that if the search value (2 in our case) is greater than the maximum value returned by 1/SEARCH(\$F\$2:\$F\$4,B2), then the search happens from right to left. In doing so, the formula ignores errors, text and blanks. So wherever, it find a number, the search would stop and return the result from column F. Hope this clarifies.

I do not understand your second question.

Thanks

For the second question – I wanted to say, we used the formula for a list of words which I have. But what if I don’t have the list of words and I want to find the same words in the sentence.

• You are welcome. You will have to provide a list of words – there is no option.

Thanks a ton Ashish once again and I will conclude my query session. Once Again, You are Fantastic. To tell you, I have subscribed to your Rss feed and I keep on getting updates whenever you update a new calculation steps and they are simply amazing. Thank you so much once again

• You are most welcome and thank you for your kind words.

• Mellisa says:

• kyur shah says:

hi ashish,

i have a list of SKU numbers which are alphanumeric and i want to lookup and extract the number from that SKU in another coloum.

eg SKU – KSOCT22W

desired result: 22
24

and so on,,,Thank you.

• Hi,

In Excel 2013 and higher versions, try this

1. Let’s assume your data is in range A2:A10
2. In B2:B4, type your expected result. IN your case, it will be 22 and 24
3. Select B2:B10 and go to Data > Flash Fill

Hope this helps.

• Houssam says:

I have this string ”

Total: 363, Ongoing: 46, Completed: 305

i want to have 365 in separate Colum and 46 in another one , etc…

thank you
Hossam

• Vipin says:

Hi, not able to access workbook for telephone number solution. Please share how can I access the workbook.

• Hi,

I have just changed the link. Could you retry. If it still does not work, then may be your firewall is blocking the download.

Hi Ashish,
Want the formula for your telephone number solution for entries upto 2500

• Kata says:

Hi, I have a problem. My Ph.N. is in very very long text strin with spaces, line breaks etc. and is 6 to 13 digits, sometimes with delimiter (dash, space) sometimes without:
“Name Surname

Telefon:
OIB:”

As you can see, sometimes there are no data.
I have found a vba to extract e-mail but been searching for days for a formula or vba for phone number but have found nothing.

• Hi,

Your question is not clear. Share a few examples and for each example also show the expected result.

• Kata says:

“Name surname

Telefon:
OIB:”
“Name surname

Telefon: 09x xxx xx xx
OIB: 12345678910”
“Name surname

Telefon: 02x/xxx-xxx
OIB:”
“Name surname

Telefon: 09x/xxx/xxx
OIB: 98765432101″

• Hi,

I still do not understand. Share the link from where I can download your Excel file and also show the expected result there.

• Kata says:

Hi to be honest I don’t knoe where to put the file so you could download it. I could sent it to you by mail?
But thanks for the effort !!!
Really, thank you.

• Hi,

• Hi,

I inserted a column after column F (Mother) and in cell G3, I typed the expected result i.e. 09x xxx xx xx. Do the same in a couple of more cells (say in G4:G5). Select G3 to the last cell in column G where you want to see the result and go to Data > Flash Fill.

• Kata says:

And that’s it?!? I spent days trying to figure it out, formulas, split text in data tab…
Thank you so much.

Now I have a problem with a different workbook, two of the, but I’m embarrassed to ask.

• You are welcome. Please feel free to ask. If your question is about some data extraction, then post it in the Comments section of this Blog article. Else browse through the other Blog articles on my website and post your question in the Comments section of the relevant Blog article.

• Kata says:

Well I wouldn’t now where to aks… I need to copy data from one workbook to an other based on one criteria.
Can you give me a hint where to look or ask?

Thank you so very much!!!

• Kata says:

Thank you so much for everything.