Extract number from an alphanumeric string

{ 72 Comments }

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.

Leave a Comment

Your email address will not be published.

*

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

  • 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

  • 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,

    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,

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

  • 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,

    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.

      • 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, 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 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,

        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

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

          • 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

  • 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, 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?

  • “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 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,

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