How to extract number from string in Excel

The tutorial shows how to extract number from various text strings in Excel by using formulas and the Extract tool.

When it comes to extracting part of a text string of a given length, Excel provides three Substring functions (Left, Right and Mid) to quickly handle the task. When it comes to extracting a number from an alphanumeric string, Microsoft Excel provides… nothing.

To get a number from a string in Excel, it takes a little ingenuity, a bit of patience, and a bunch of different functions nested into each other. Or, you can run the Extract tool and have the job done with a mouse click. Below you will find full details on both methods.

How to extract number from the end of text string

When you have a column of alphanumeric strings where number always comes after text, you can use one of the following formulas to get it.

Important note! In the below formulas, the extraction is performed with the RIGHT and LEFT functions, which belong to the category of Text functions. The output of these functions is always text. In our case, the result will be a numeric substring, which in terms of Excel is also text, not number. If you need the result to be a number (that you can use in further calculations), wrap a corresponding formula into the VALUE function as shown in the first example.

Formula 1. Get number from the right of a string

To extract number from a 'text-number' string, the first thing you need to know is where to start the extraction. So, let's determine the position of the first digit with this generic formula:

MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))

We will dwell on the formula's logic a bit later. For now, simply replace cell with a reference to the cell containing the original string (A2 in our case), and enter the formula in any empty cell in the same row, say in B2:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

Although the formula contains an array constant, it's a regular formula completed in the usual way by pressing the Enter key.

Once the position of the first digit is determined, you use the RIGHT function to extract the number. To find out how many characters should be extracted, you subtract the position of the first digit from the total length of the string, and add one to the result because the first digit is also to be included:

=RIGHT(B2, LEN(A2)-B2+1)

Where A2 is the original string and B2 is the position of the first digit.

The following screenshot shows the results:
Formula to extract number from the right of a string

To eliminate the helper column containing the position of the first digit, you can embed the MIN formula directly in the RIGHT function like this:

=RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1)

To force the formula to return a number rather than a numeric string, nest it into the VALUE function:

=VALUE(RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1))

How the position of the 1st digit is calculated

We supply the array constant {0,1,2,3,4,5,6,7,8,9} in the find_text argument of the SEARCH function, so that the formula searches for each element of the array within the original string and returns their positions. Because the array constant contains 10 digits, the resulting array also contains 10 elements.

The MIN function processes the resultant array and returns the smallest value, which corresponds to the position in of the first digit in the original string.

Additionally, we use a special construction (A2&"0123456789") to concatenate every possible number with the original string. In situations when a certain number in the array constant is not found within the source string, this construction acts like IFERROR forcing the formula to return a "fake" position equal to the string length +1 or more chars. As the result, if the original string does not contain any number, like in row 7 in the screenshot above, the RIGHT formula returns an empty string.

To make things easier to understand, let's see how the formula works out for a specific cell, say A2. That cell contains the text string "ECDAA-05", for which the SEARCH function returns the following array {7,10,11,12,13,8,15,16,17,18}. Here's how:

  • 0 is the 1st element of the array constant and the 7th character in the original string, therefore the first item of the resulting array is "7".
  • 5 is the 6th element of the array constant and the 8th character in the original string, so the sixth item of the resulting array is "8".
  • No other item of the array constant is found in cell A2, and therefore the other 8 items of the resulting array represent the positions of corresponding digits in the concatenated string (ECDAA-050123456789).

Getting the position of the 1st digit in a string

Since 7 is the smallest value in the resulting array, the MIN function returns it, so we get the position of the first digit (0) in original string.

Formula 2. Pull number from the right of a string

Another way to extract number from the end of a string is by using this generic formula:

RIGHT(cell,SUM(LEN(cell) - LEN(SUBSTITUTE(cell, {"0","1","2","3","4","5","6","7","8","9"},""))))

With the original text string in A2, you enter the below formula in B2 or any other empty cell in the same row, and then copy it down the column:

=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))

Another formula to get number from the end of a string

How this formula works

In essence, the formula searches for all numbers from 0 to 9 within the source string, counts the found numbers, and returns that many characters from the end of the string.

And here is the detailed formula break down:

  • First, you use the LEN and SUBSTITUTE functions to find out how many times a given number occurs in the string. For this, you replace the number with an empty string (""), and then subtract the length of the string without that number from the total length of the original string. This operation is performed on each number in the array constant.
  • Next, the SUM function adds up all occurrences of all digits in the string.
  • Finally, the digit count goes to the num_chars argument of the RIGHT function instructing it to return that many characters from the right side of the string.

How to extract number from the beginning of text string

If you are working with strings where text appears after number, the formula for extracting number would be similar to the one discussed above, with the difference that you use the LEFT function to pull the characters from the left side of the string:

LEFT(cell,SUM(LEN(cell)-LEN(SUBSTITUTE(cell,{"0","1","2","3","4","5","6","7","8","9"},""))))

With the original string in A2, use the following formula to get number:

=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))))

Formula to extract number from the beginning of text string

Note. As is the case with the RIGHT function, the LEFT function returns a numeric substring, which is technically text, not number.

How to get number from any position in a string

If your task implies extracting number from anywhere in a string, you can make use of the following mind-boggling formula published on MrExcel forum:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Where A2 is the original text string.

Breaking down this formula would require a separate article, so you can simply copy it to your worksheet to make sure it really works :)
Formula to get number from any position in a string

Upon examining the results, however, you may notice one insignificant drawback - if the source string does not contain a number, the formula returns zero, as in row 6 in the screenshot above. To fix this, you can wrap the formula in the IF statement, the logical test of which checks if the source string contains any number. If it does, the formula extracts the number, otherwise returns an empty string:

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

As shown in the screenshot below, the improved formula works beautifully (kudos to Alex, our Excel guru, for this improvement):

An improved formula to extract number from anywhere in a string

Unlike in all previous examples, the result of this formula is number. To make sure of this, just notice the right-aligned values in column B and truncated leading zeros.

Extract number from text string with Ultimate Suite

As you have just seen, there is no trivial Excel formula to pull number from a text string. If you have difficulties with understanding the formulas or tweaking them for your data sets, you may like this simple way to get number from string in Excel.

With our Ultimate Suite added to your Excel ribbon, this is how you can quickly retrieve number from any alphanumeric string:

  1. Go to the Ablebits Data tab > Text group, and click Extract:
    Extract tool for Excel
  2. Select all cells with the source strings.
  3. On the Extract tool's pane, select the Extract numbers radio button.
  4. Depending on whether you want the results to be formulas or values, select the Insert as formula box or leave it unselected (default).

    My advice is to select this box if you want the extracted numbers to update automatically as soon as any changes are made to the source strings. If you want the results to be independent on the original strings (e.g. in case you plan to remove the source data at a later point), then do not select this box.

  5. Click the Insert Results button. Done!

Extract numbers and insert the results as formulas or values.

Like in the previous example, the results of the extraction are numbers, meaning you are free to count, sum, average, or perform any other calculations with them.

In this example, we've chosen to insert the results as values, and the add-in did exactly what was asked for:
Numbers are extracted from strings as values.

If the Insert as formula checkbox was selected, you'd observe a formula in the formula bar. Curious to know which one? Just download Ultimate Suite's trial and see for yourself :)

If you'd like to have this as well as 60+ more useful tools in your Excel, then leverage this special opportunity that we provide exclusively to our blog readers:

Get Promo Code for Ultimate Suite - exclusive offer for our blog readers!

Available downloads

Excel Extract Number - sample workbook (.xlsx file)
Ultimate Suite - trial version (.zip file)

238 responses to "How to extract number from string in Excel"

  1. Sumit KK says:

    Hey,
    Svetlana Cheusheva
    Please tell me how I can make a live input cell that is
    "Underlying Index: NIFTY 11907.30 As on Jul 03, 2019 10:05:20 IST" in B1 CELL

    to another cell say in C1 11907.30 and time in C2 10:50:20 to another cell.
    or at least I can print C1 that would be also sufficient.

    mainly viewed all the comment but not able to figure the amount

  2. Brian B says:

    This was immensely helpful, thanks. How do I get it to preserve any leading zeroes?

  3. Brian B says:

    I meant, how do I get the last method (extract from anywhere) to preserve leading 0's? Thanks.

  4. Amit says:

    Hi,
    This is very helpful.
    One thing, the SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"") doesn't seem to be working for me. It is not replacing the characters with empty string.
    Data in cell A2: 25R
    Expected: R

  5. Cenzo Dontworryboutit says:

    This is a great post.
    thank you so much for your effort here. I wish my company wasn't so tight in the pockets so I could get all those add-ins

  6. Dee says:

    Ram mobile no-9925923457. Resides In Noida 119961
    Can You suggest How To find Phn no. From Above Text

  7. Deepak says:

    Client Name
    LALITA
    GEETA DEVI NAYAK
    MEHARUN NISHA
    DIPA MANOJ
    PREETI SINGHAL
    meena devi swami
    RAJIYA BEGAM
    SHEHIDE
    TARAWATI
    BHATERI DEVI
    sheela devi
    JANKI DEVI
    SUNITA
    ALKA KANWAR
    JAITUN
    POOJA DEBI
    CHHOTI DEVI
    VIMLA DEVI
    manju devi tak
    MANJU
    MUNNI DEVI
    GEETA DEVI
    TULSI DEVI
    AILARAKHI
    MUMTAJ BIBI
    How to Find MID name if mid name more than 3 Character

    • Prakash says:

      =IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>1,MID(A2,FIND(" ",A2,1)+1,SUM(FIND(" ",A2,FIND(" ",A2,1)+1),(FIND(" ",A2,1)+1)*-1)),"")

  8. sahara says:

    The formula, of middle search doesnt fetch n give decimal place like 18.625

  9. Fred Rogers says:

    Not sure how to extract check no’s. From a text string having more that one set of numbers - see example below:

    Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1

    Any suggestions are greatly appreciated - the line above is a sample of the excel items and after the check number shown there a number of invoice numbers in the text string.

    Thanks , Fred

    • Jayanth says:

      If this is the data :
      My Assumption is that all your data has "/" after the check number.
      First :
      Find the nth place of that "/" in that string using this formula :
      =+FIND("/",D2)
      Brad James Company - Check - 23897 / invoice # 456755 issued Sept 1
      Output = 36
      then,
      use this formula =+MID(D2,C2-7,7)
      here D2 is the input data which you have & C2 refers to the output of find formula i.e)36
      then the output will be "23987".
      Hope this helps! :)

  10. Nilka F says:

    This formula is exactly what I was looking for... and it works perfectly. Thank you so much! ;-)
    =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

  11. Prashanth V says:

    Hey,
    Svetlana Cheusheva
    Thank you so much for the tutorial. It is very nice of you. It was very helpful to me.

  12. Kanchan says:

    While this was very very helpful, I am facing a new kind of problem.
    Job Id - #2416387528594195 is getting converted to '2416387528594190, excel is rounding off the last two digits and replacing with 0. Concat with an apostrophe also didn't help. I applied formula: =CONCAT("'",SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*ROW(INDIRECT("1:"&LEN(B2))),0),ROW(INDIRECT("1:"&LEN(B2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B2)))/10))

  13. Sekar says:

    This formula does not give the decimal values i.e 5.25, 7.3 and more. Kindly help me out on this.

    Formula ,

    IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

  14. Kalyan Mondal says:

    =SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3)))*ROW(INDIRECT("1:"&LEN(A3))),1)),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A3)))-1))

    not getting any result if help if there is any error

  15. Thomas says:

    Thanks for the formulas (But I had trouble get them working. Here is why)
    In some countries, Sweden among them, the "," character is a decimal delimiter. There for "SEARCH({0,1,2,3,4,5,6,7,8,9},A2)" results in an error. So for us we have to use another character in the syntax, ";". So here is what worked for me:
    SEARCH({0;1;2;3;4;5;6;7;8;9},A2)

    Hope above saves some time for others!

  16. Excel is Powerfull T says:

    The article was such Helpful that I got rid of a work that could have required me to dedicate lots of minutes. Thank you all.

  17. Johannes says:

    How can add the numbers of this following examples, without sorting it fisrt
    CIVUS0.35B
    CIVUS1W
    IA-CIVUS0.13W
    TAVUS0.35SB
    AVSSF2W/B
    AVS3W/B

  18. Paul Flores says:

    hi i have a cell from vendor quotes that i receive. this is how i name the vendor quote.
    Lighting Expression 11-20-19 ($132,833)
    Vendor name - date provided - dollar amount
    i want to extract only the dollar amount which can vary up to 2 million.

  19. Saurabh says:

    Below formula working fine but some number have percent sign between the text,i want extract number with percent sign which have % Sign as well.

    =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

  20. Jeremy L Hines says:

    What is a good formula to use in order to see numbers that are alike in any order? For example
    457. 885
    886. 275
    573. 547

  21. Laszlo Bogdan says:

    Thanks for this great solution. The formula given above ("How to get number from any position in a string")-> is work fine.

  22. Chris Acosta says:

    Hello, what formula can I use to pull and separate the last four sets of numbers in the following string?
    "CJFRO20190047 000 004 03/14/19 1906 JP MORGA JPMC 1st Qtr 0.00 0.00 0.00 -12.63"
    I used the first formula from above but it doesn't give me all the values.
    Any advice will be greatly appreciated.

  23. Fahad Alkhaldi says:

    What would be the number if the invoice number has alphanumeric characters in the middle.
    Example:
    09187GH1234

  24. Kevin Doan says:

    Hi How do I find the MAX numerical value of the alphanumeric string? for example:
    X-0100
    B-0213
    F-0505
    Z-0111
    to show that F-0505 is the high value in the column

  25. Peter says:

    Hi, could someone please help?
    trying to extract the size from the following:
    KIERRASTONE ASH TEXTURED ZKI2655A 300 X 600 X 9MM
    (300 X 600 X 9MM)
    i dont want the numebrs with the text (zki2655a) only the 300 X 600 X 9MM
    thanks look forward to your reply :)

  26. vinay says:

    how can i extract set of 6 number form the below string
    "LBS 28 Marg, Bhandup West, Mumbai 400078, Maharashtra"

  27. Grant says:

    B264 80 0172760 STAINLESS STEEL HEX HEAD BOLT ASSEMBLY, 1/2" X
    What if I'm needing to pull only a seven digit number out of a string of text. I only need the 0172760 from the text. The problem I'm having is that the position of this seven digit number is not consistent from cell to cell and the previously mentioned formulas don't apply because they pull all digits, not just ones with a certain length.

  28. PRAJWAL BHATTARAI says:

    what is the appropirate formula to find mid value (i.e. 602969) of FP:ADBL5-602969-2830 starting from "FP" among the spread sheet.

  29. Nitin says:

    Hi All,
    Can you please help me extract this six digit number.

    clg:ramanlal/chennai/012345/April

  30. Mayank says:

    -6.135474.10.00.100012-AziziDevelopments-WO-1-73944857464-CONTR0067799835-Inet
    I want to extract only this portion "6.135474.10.00.100012" and some thing like that number from rest of data of 3000. Can anyone help me please with the formula.

  31. Terry says:

    THANK YOU SO MUCH THIS SAVED MY LIFE

  32. vino says:

    hi,
    Eg: One column 20Pcs Disposable Filter 3 Ply mask and another column 20
    how to find the same number exist in that string is correct?
    I have tried SEARCH option but is show only position. i want the exact number found in both the column is right/wrong?

  33. Mayank says:

    Hi Team,
    -6.135474.10.00.100012-AziziDevelopments-WO-1-73944857464-CONTR0067799835-Inet

    I want to extract only this portion "6.135474.10.00.100012" and some thing like that number from rest of data of 3000. Can anyone help me please with the formula.

  34. bhakti says:

    This formula is working but the output is in form of exponential format. I am trying to remove GL code only, but it does not seem to be working.
    Here is the example:
    Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69891 · FICA
    Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69895 · FUTA
    Resident Care:69000 · Wellness:69800 · Salaries and Wages:69890 · Payroll Taxes:69897 · MI-UIA

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  35. Learner says:

    I want o extract text from number like:
    1. 100Rte02T------RTet
    how can i do that by using formula

  36. nrml says:

    very helpful but please make practice sample files available.

  37. Krystian says:

    I am wondering if this formula can be applied for address street number extraction, wherein the address line you have multiple numbers. For example:

    "Rua Hungria, 1240 – Jd. Europa | 1º andar" = 1240 & 1 = 12401

    And so, I was hoping for a solution to insert "-" between every occurring number. Thoughts?
    This is really great post! Thanks.

    • Hello Krystian!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

      • Krystian says:

        Hey Alex!

        I am using this formula:
        =SUMPRODUCT(MID(0&I25, LARGE(INDEX(ISNUMBER(--MID(I25, ROW(INDIRECT("1:"&LEN(I25))), 1)) * ROW(INDIRECT("1:"&LEN(I25))), 0), ROW(INDIRECT("1:"&LEN(I25))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(I25)))/10)

        And here is the example of raw data:
        R LEOPOLDO COUTO DE MAGALHAES JUNIOR, 758 - ANDAR: 15; CONJ: 151;

        Using the formula on the above example, I am getting this: 75815151 - concatenated numeric value of all numbers from the string. And, what I am hoping is to add a special character that would show the numbers like so: 758-15-151
        In short, on top of extracting the numbers, differentiate multiple numbers by some special character, "-" for example.

        Let me know if this is better. Thanks!

        • Hello Krystian!
          You can use a custom format using the TEXT function

          =TEXT(A1,"###-##-###")

          where A1 is the cell with your formula.
          Or use your formula in the TEXT function

          =TEXT(SUMPRODUCT(MID(0&I25, LARGE(INDEX(ISNUMBER(--MID(I25, ROW(INDIRECT("1:"&LEN(I25))), 1)) * ROW(INDIRECT("1:"&LEN(I25))), 0), ROW(INDIRECT("1:"&LEN(I25))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(I25)))/10),"###-##-###")

          I hope this will help, otherwise please do not hesitate to contact me anytime.

  38. kishen says:

    hi there,
    how do i extract any number before a decimal point using a formula.
    meaning 5569.9008 i only want to extract 5569. the formula has to be across for any types of decimals and combination numbers. thank you for the assistance.

  39. Shashikumar H says:

    Dear
    for example : 25,20,15,25,300,40 is it possible to extract the numbers before "g",
    Ali Baba Dark Chocolate 25 gm box 12 pcs
    Ali Baba Dark Chocolate 20gm*24 box
    Cadbury 5 Star White Chocolate 15gm
    Kinder 2 White Chocolate 25 gm*24
    ALpella Biscuits W/Marshmallow300gm
    Alpella Chocolate 40gm
    plz let me know the formula

  40. Melissa says:

    Thanks for the formula - it works, and it's going to save me a ton of time!

  41. Sichuan says:

    Hi alexander,
    How do i extract number from 113°53'42" to 1135342 ?
    Please let me know the formula
    Thanks before.

  42. James Raymond Bonafe says:

    Do you have formula, including the decimal point.
    Sample: RT-12.5BT, RG5.7T
    Because when I use the formula result is
    Result: 125, 57

  43. Lee Joo Hyun says:

    I have read well on how to extract numbers from the beginning of a text string.
    However, even if there are additional numbers in the middle of the text string, I want to extract only the characters at the beginning in addition to the additional numbers. In other words, if you have a number in the middle of a text string (if the number ends and there is another number after the letter), you want the result to remain unchanged, but the formula provided does not. Is there a possible formula?

  44. Lee Joo Hyun says:

    Sorry but,
    I want
    25600aaa BBB / 25 * 35 * 46cm
    ->
    25600

  45. Lee Joo Hyun2 says:

    Sorry but,
    35600aaa bbb/ccc/25*36/450 // Original text.
    35600 (O) // The answer I want.
    35600aaa bbb(X) // When applying the provided formula.
    Can you make an extended formula that meets my requirements?

  46. Ashok says:

    this is the number 15060277631602300000,
    i want to separate it like that 1506027763//1602300000
    whats the formula for this .please help

  47. Razi says:

    Dear Expert Users
    Please help anybody for get area from 250x350 that is written in one cell
    and area should be 87500.

  48. Kritika says:

    Please could you help me with a formula that can extract number from 9 year(s), 11 month(s),
    and add a decimal point after years.
    Q1- 9 year(s), 11 month(s),
    Answer from formula - 9.11

  49. Pramod Bagave says:

    500-555-0172
    325-555-0137
    582-555-0148
    1 (21) 500 555-0145
    1 (12) 500 555-0117
    615-555-0153
    926-555-0182
    1 (22) 500 555-0140
    1 (11) 500 555-0190
    961-555-0122
    740-555-0182
    775-555-0164

    Write a formula to extract the numbers, eliminating all the spaces symbols state codes

    • Hello!
      Formula to extract the numbers, eliminating all the spaces symbols and codes —

      =CONCAT(IF(ISNUMBER(--MID(REPLACE(A2,1,IFERROR(FIND(")",A2,1),1),""), ROW($1:$93),1)), MID(REPLACE(A2,1,IFERROR(FIND(")",A2,1),1),""), ROW($1:$93),1),""))

      I hope my advice will help you solve your task.

  50. Punith Raju says:

    This Formula is working out for me. But is there any solution that I can sumup the values.

    Example: 1apple&2orange = 12 (The answer what I am getting as of now but I need to sumup & get "3" as a answer)

    Please help me with this.

    =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

  51. Punith says:

    Working Fine.
    =CONCAT(IF(ISNUMBER(--MID(A4,ROW($1:$93),1)),MID(A4,ROW($1:$93),1),""))

    Using the formula
    1orrange&2apple = 12 ( Answer getting now)

    I need the answer as
    1orrange&2apple = 3 ( it suppose to add up the numbers)

  52. Svetoslav says:

    Thank you, it's works!

  53. Kenny Bird says:

    I am trying to pull just $ amount with Decimals and commas in this sentence how would I do that

    Paying total amount of $ 12,275.21

    Thanks in advance

  54. Bagas Afdhala says:

    ILH-E-AC-030
    ILH-E-AC-031
    ILH-E-AC-032
    ILH-E-AC-033
    ILH-E-LO-003 SHT1
    ILH-E-LO-003 SHT2
    ILH-E-LO-027 SHT1
    ILH-E-LO-027 SHT2
    ILH-E-LO-027 SHT3

    i want to extract this to other cell so it look like:
    030
    031
    032
    033
    033
    033
    027
    027
    027

    can someone tell me the formula to extract just the 3 digits number after the last "-" from left?

  55. SAJJAN says:

    Hi alexander,
    How do i extract number from

    1 - 123, Singh Petrol Pump, Bishrampur, 497226, 36
    2 - Company, 123, 123, 788031, 123
    3 - 234, Danapur Maruti Suzuki Agency, Gopalganj, 841427, Bihar
    4 - Plot No RM-126,R & C Zone,, MIDC INDL. Area, Butibori. Dist Nagpur, 441122, 27- Maharashtra

    FOR 1ST ROW I WANT 497226
    FOR 2ND ROW I WANT 788031
    FOR 3RD ROW I WANT 841427
    FOR 4TH ROW I WANT 441122

    Please let me know the formula

  56. GLORIA ASHIEKAA says:

    Hello,
    I would like to extract the phone numbers from this cell.

    7. UZOUKWU, PRINCE ROYCE 0803 743 5119-MUM/0803 275 9140-DAD

    I have a long spreadsheet of names & the positioning of the phone numbers are not in the same place.

    However I will separate these phone numbers in 2 cells.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard