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.

Magnificent! (As usual. 😛 )

Thank you. Glad you liked my article

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.

Thank you.

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.

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

Thank you for your kind words. Glad to Help.

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…

Hi,

You may refer to my solution in this workbook.

Hope this helps.

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.

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.

Wow! Impressive! Thank you so much!

You are welcome.

Superrrrrrrrrrrrrr formula

Thank you.

Your answer is great but I want to extract numbers from d5d6s4g6s43w85c344 please help me out

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.

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.

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.

thanks in advance!

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.

Hi,

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

Thanks in advance!

The result of my formula should be a number itself.

No, Unfortunately it give me either 0 or 1.

0 and 1 are numbers. What exact result are you expecting?

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

Hi,

Please share the exact result which you are expecting.

Hi,

Could you please help in this.

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

Appreciate your help.

Hi,

Will there always be the letters T, M and B?

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

Hi,

I can help with a formula base solution, if interested.

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.

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,

Share the link from where I can download your workbook. Also, show the expected result there.

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,

Prasad. D.

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.

your uncomplicated way of implementing your knowledge is super helpful and fantastic! Million thanks!

You are most welcome. Thank you for your kind words.

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

SADR24B

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.

I have this string "

Total: 363, Ongoing: 46, Completed: 305

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

please help me in formula

thank you

Hossam

Hi,

You can solve this quite easily with Data > Flash Fill.

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

Hi,

Share some data and show the expected result.

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:

e-adresa:

Adresa:

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.

Can ou please help?

Hi,

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

"Name surname

Telefon:

e-adresa:

Adresa:

OIB:"

"Name surname

Telefon: 09x xxx xx xx

e-adresa: @gmail.com

Adresa:

OIB: 12345678910"

"Name surname

Telefon: 02x/xxx-xxx

e-adresa:

Adresa:

OIB:"

"Name surname

Telefon: 09x/xxx/xxx

e-adresa: xxxxx.xxxx@gmail.com

Adresa:

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.

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,

Upload the file to OneDrive/Google Drive and share the download link here.

https://drive.google.com/file/d/1kn1bOtsMdziwNbASTKafx166THuoVHkd/view?usp=sharing

I hope that it.

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.

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.

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!!!

Hi,

You will need a VBA solution for this. I do not provide VBA support. You may post your question in the MS Excel forums of Microsoft Community - https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?sort=lastreplydate&dir=desc&tab=threads&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=all&tm=1474341650876

Thank you so much for everything.

You are most welcome.