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 numbers 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 comes after text, you can use the following formula to get it.

RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

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:

=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))

This formula gets number only from the end. If a string also has numbers in the beginning or middle, they are ignored:
alt=

The extraction is performed with the RIGHT function that belongs to the category of Text functions. The output of this function is always text. In our case, the result is 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), then wrap the formula into the VALUE function or perform an arithmetic operation that does not change the result, say, multiply by 1 or add 0. To catch errors in the strings that do not contain a single number, use the IFERROR function. For example:

=IFERROR(VALUE(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))), "")

or

=IFERROR(RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0))) +0, "")
An improved formula to extract number from the end of a string

Note. In Dynamic Array Excel (Office 365 and 2021), you enter the formula in the usual way with the Enter key. In Excel 2019 and earlier, it only works as an array formula, so remember to press Ctrl + Shift + Enter to complete it.

How this formula works:

To extract number from an alphanumeric string, the first thing you need to know is where to start the extraction. The position of the last non-numeric character in a string is determined with the help of this tricky formula:

MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0))

To understand the logic, let's investigate it from the inside:

The ROW(INDIRECT("1:"&LEN(A2))) combination creates a sequence of numbers corresponding to the total of characters in the source string (A2), and we serve these sequential numbers to MID as the starting numbers:

MID(A2, {1;2;3;4;5;6;7;8}, 1)

The MID function pulls each individual character from A2 and returns them as an array:

{"0";"5";"-";"E";"C";"-";"0";"1"}

As MID is a text function, its output is always text (as you can notice, all the characters are enclosed in quotation marks). To turn numeric ones into numbers, we multiply the array by 1 (double negation --MID() will have the same effect). The result of this operation is an array of numbers and #VALUE! errors representing non-numeric characters:

ISNUMBER({0;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1})

The ISNUMBER function evaluates each element of the array and gives its verdict in the form of Boolean values - TRUE for numbers, FALSE for anything else:

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

This array goes to the logical test of the IF function, where each element of the array is compared against FALSE:

IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)

For each FALSE (non-numeric value), another ROW(INDIRECT()) function returns its relative position in the string. For each TRUE (numeric value), a zero is returned. The resulting array looks as follows:

{0;0;3;4;5;6;0;0}

The rest is easy. The MAX function finds the highest number in the above array, which is the position of the last non-numeric value in the string (6 in our case). Simply, subtract that position from the total length of the string returned by LEN, and pass the result to RIGHT to let it know how many characters to extract from the right side of the string:

RIGHT(A2, LEN(A2) - 6)

Done!

How to extract number from the beginning of text string

If you are working with records where text appears after number, you can extract number from the start of a string by using this generic formula:

LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

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

=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)

No matter how many digits are in the middle or end, only the starting number is extracted:
Formula to extract number from the beginning of text string

Note. In Excel 365 and Excel 2021, due to support for dynamic arrays, a regular formula works fine. In Excel 2019 and earlier, you should press Ctrl + Shift + Enter to explicitly make it an array formula.

How this formula works:

Here, we again use the combination of ROW, INDIRECT and LEN functions to create a sequence of numbers equal to the total of characters in the source string plus 1 (the role of that additional character will become clear a bit later).

ROW(INDIRECT("1:"&LEN(A2)+1))

MID and ISNUMBER do the same job as in the previous example - MID pulls individual characters and ISNUMBER converts them to the logical values. The resulting array of TRUE's and FALSE's goes to the MATCH function as a lookup array:

MATCH(FALSE, {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}, 0)

MATCH computes a relative position of the first FALSE, giving us the position of the first non-numeric character in the string (3 in A2). To extract the preceding numbers, we subtract 1 from position the first text character and serve the difference to the num_chars argument of the LEFT function:

LEFT(A2, 3-1)

Now, back to an "extra" character in the sequence generated by ROW(INDIRECT()+1)). As you already know, this sequence provides the starting points for the MID function. Without +1, MID would extract exactly as many characters as there are in the original string. If the string contains only numbers, ISNUMBER will return only TRUE's while MATCH needs at least one FALSE. To ensure that, we add one more character to the total length of the string, which the MID function would convert to an empty string. For example, in B7, MID returns this array:

{"1";"2";"3";"4";""}

Note. As is the case with the RIGHT function, LEFT also returns a numeric substring, which is technically text, not number. To get the result as a number rather than a numeric string, nest the formula in the VALUE function or multiply the result by 1 as shown in the first example.

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.

Tip. In Excel 365 - Excel 2019, there is a much simpler solution with the help of the TEXTJOIN function. Please see How to remove text and keep numbers.

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 :)

Available downloads

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

You may also be interested in

356 comments to "How to extract number from string in Excel"

  1. Lief M says:

    Hi there. I appreciate the detailed approach to this challenge. I have the following text:

    "[SW] 183 Patterson -- WAN1 DOWN -- PL"

    I'm using the formula above "How to get number from any position in a string"

    Instead of returning 183, it returns 1831 - adding an extra 1 at the end?

    Another text:

    [SW] 016 Bremerton -- Router DOWN -- PWR" returns 16 instead of 016?

    All thoughts are very much appreciated.

    • Hello!
      Your first example has 4 digits. All of them are returned by the formula. In the second example, the result is returned as a number, not as text. Excel removes leading zeros from numbers.
      To extract numbers as text, try this formula:

      =CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$100),1)),MID(A2,ROW($1:$100),1),""))

      You can also extract numbers from text using regular expressions (Regex). I recommend this tutorial with examples: Regex to extract number from string.

  2. Mansour says:

    Hi,

    Thanks for sharing this very helpful code. I have a problem with number that has digit like 1.5 or 3.70 it turn to 15 and 370. Could you please guide me how to fix that :)

    Thanks again!

    • Hello!
      To extract a number with a decimal separator from text, use the formula:

      =CONCAT(IFERROR(MID(A2,(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))+ (MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1)="."))* ROW(INDIRECT("$1:$"&LEN(A2))),1),""))

      I hope I answered your question.

  3. Courtney says:

    This formula looks so close to what I need. I'm wondering if someone wouldn't mind showing me how to tweak it to suit my requirement?

    We receive many payments each day with various narratives, I would like to automatically identify references which are 7 digit number beginning with "13" or "14". Examples below and to the right is the currently formula result.

    I am using this formula, essentially copied from above comments and info - I think I need to have the result only report back when the sequence beginning with "13" or "14" is 7 digits in length.

    Any thoughts?

    =IF(SUM(LEN(A22)-LEN(SUBSTITUTE(A22, {"13","14"}, "")))>0, SUMPRODUCT(MID(0&A22, LARGE(INDEX(ISNUMBER(--MID(A22,ROW(INDIRECT("$1:$"&LEN(A22))),1))* ROW(INDIRECT("$1:$"&LEN(A22))),0), ROW(INDIRECT("$1:$"&LEN(A22))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A22)))/10),"")

    PAYMENT FROM MR SIMON ZHAO LPSPR 8486 No result-correct
    TRANSFER 1322500 midas prch FROM SOON LEGAL 1322500-Correct
    TRANSFER Ref 1401272 Susann FROM J BOGAN 1401272-Correct
    PAYMENT FROM MS NILU JONCHHE Lot 136/Carseldine + Lot136/Carseldine 136136 - Incorrect
    0491400550 HORNSBY 491400550-Incorrect
    TRANSFER 1398031 FROM SETTLEMENT FUNDS 1398031-Correct

    Thank you

    • Hi!
      To extract 7 digit number beginning with “13” or “14” from text, you can use the formula

      =(MID(A2,SEARCH(" 13",A2)+1,7)* ISNUMBER(--MID(A2,SEARCH(" 13",A2)+1,7)))* ((--MID(A2,SEARCH(" 13",A2)+1,7)* ISNUMBER(--MID(A2,SEARCH(" 13",A2)+1,7))) > = 1000000)*(MID(A2,SEARCH(" 13",A2)+8,1)=" ")

      For the number "14", replace "13" with "14" in the formula.
      You can also use regular expressions to extract numbers from text.
      At the link above you will find detailed explanations and examples of how to extract text and numbers from a string. Here is an example formula that will solve your problem:

      =RegExpExtract(A1&" "; "\s[1][34]\d{5}\s")

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  4. Eddie says:

    Hi, i need to extract a group of text from a cell, and sum them together. before i begin, i would like to clrify that, I am not an Excel pro user like accountant. I'm using it as a data link between AUTOCAD and EXCEL. so, please bear with me.

    A B c
    1 YES 1800/ 900/ 1200 X 500 3900
    2 NO 600/ 100/ 300/ 20 X 15 0

    YES and NO is in column A (row 1 and 2 respectively). while the number is in column B. and the sum will appear in column C next to each row. i'm looking for a formula to extract 1800, 900, and 1200 to sum it up into 3900 (1800+900+1200), and it only extract and sum it when the column A says "YES", and will not do the sum when A says "NO".

    • Eddie says:

      sorry, the sample text arrangement went wrong.

      YES 1800/ 900/ 1200 X 500 = 3900
      NO 600/ 100/ 300/ 20 X 15 = 0

      • Hi!
        You can extract numbers from text using substring functions with these formulas:

        =--LEFT(B1,SEARCH("/",B1)-1)
        =--MID(B1,SEARCH("/",B1)+1,SEARCH("/",B1,SEARCH("/",B1)+1)-1-SEARCH("/",B1))
        =--MID(B1,SEARCH("/",B1,SEARCH("/",B1)+1)+1,SEARCH("X",B1)-1-SEARCH("/",B1,SEARCH("/",B1)+1))

        Write the condition using the IF function:

        =IF(A1="YES",=--LEFT(B1,SEARCH("/",B1)-1),"")

        General formula for three numbers with condition:

        =IF(A1="YES",LEFT(B1,SEARCH("/",B1)-1)+ MID(B1,SEARCH("/",B1)+1, SEARCH("/",B1,SEARCH("/",B1)+1)-1-SEARCH("/",B1))+ MID(B1,SEARCH("/",B1,SEARCH("/",B1)+1)+1, SEARCH("X",B1)-1- SEARCH("/",B1,SEARCH("/",B1)+1)),"")

        • eddie says:

          i see. Thanks for your help. i will need some times to explore what you shared with me. looking at the formula you gave, is killing my brain. lol. will get back to you how it goes.

          Thanks again for sharing

  5. Numberian says:

    Hey i have some problem.

    My job is to extract number from a cell. A cell has 4 digits of randomly generated number (ABCD). So i want to automatically extract number A into one cell, B into one cell and the rest. and sometimes i need to extract 2 number from the same cell. is there a clean way to do it?

  6. Simon Velez says:

    Hi I hope you could help me!
    I have some prices i need to extract...

    📱XIAOMI📱
    📱Redmi 9A 32GB/2R 399.900 A,G🇨🇴
    📱Redmi 10 64GB 744.900 B🇨🇴
    📱Redmi 10 128GB 839.900 G🇨🇴
    📱Poco X3 128GB/8R 949.900 G,A 🇨🇴
    📱Poco X3 Pro 128GB/6R 909.900 N🇨🇴

    My question is... Is there a clean and simple way to only obtain the prices from this cellphones For example telling a formula to look for more than 4 consecutive numbers and extract them.

    Look at this...

    Redmi 9A 32GB/2R 399.900 A,G🇨🇴

    When I use a formula to look for only numbers it extracts 9322399900
    I need something capable of skiping small number secuences and search for numbers bigger than (9A) (32GB) (2R) in this case 399000
    I know that I can search for common characters like ($) $399000 to use text to columns.
    But I have a wide range of providers that dont use any particular sign for me to use.

        • Simon Velez says:

          Thanks a lot. Both of your formulas worked great. Its amazing. I post some examples to Svetlana
          Because some of my prices are in Millions and i was not able to interpret the formula to make it take into account bigger numbers. My knowledge of excel and english is very limited but I´m thankfull of your help. I´ve been doing this process the large way for about 2 years.
          I could clean all of my data to have numbers without any signs. Example:

          iPhone 13 128GB 5000000
          iPhone 13 Pro 246GB 4500000
          iPhone 12 Pro Max 512GB 3650000

          Thanks in advance to all of you for your great work!

          • Hi Simon,

            In case your prices have various delimiters like in the sample below, then cleaning the data before using the Regex formula is indeed the most effective way. As you said, first you remove all the characters between the prices (. , ’ ). And then, extract a number containing 4 or more digits using this simple regex:

            \d{4,}

    • Hello Simon,

      The only solution I can think of is using regular expressions. For this, you will need to add a custom RegExpExtract function to your workbook - the code and the detailed instructions are on the above-linked page.

      As for the regex pattern, I don't think searching for 4 consecutive numbers will work in your case, because the prices in your sample strings have a thousands separator (period). So, you can use the below regex to match a substring consisting of 1-3 digits, followed by a period (.), followed by 3 digits.

      Pattern: \b\d{1,3}\.\d{3}\b

      The above pattern will work for numbers ranging from 1.000 to 999.999. If your real dataset has a wider range of numbers, you'll have to adjust the pattern.

        • Simon Velez says:

          It worked perfectly. Could you please help me adjust the pattern for millions. I tried to interpret your formula but i couldn´t understand which is the key to increment to millions
          I got some examples

          Store A Example
          Poco X3 Pro 8/256 GB $1’050.000 N-A 🇨🇴
          Poco X3 GT 8/128 GB $1’230.000 🇨🇴
          Poco X3 GT 8/256gb $1.340.000 🇨🇴Blanco

          Store B Example
          IPHONE 12 MINI 64GB
          BLANCO
          $2.880.000

          IPHONE 12 MINI 128GB
          ROJO
          $3.100.000

          IPHONE 12 64GB
          BLANCO
          $3.300.000

          Store C Example
          11 pro max 256gb $4.200.000 negro🇨🇴🇨🇴
          12 64gb $3.350.000 blanco verde negro🇨🇴🇨🇴
          12 128gb $3.580.000 negro verde🇨🇴🇨🇴
          12 pro max 256gb $5.100.000 azul🇨🇴🇨🇴

          Store D Example
          POCO X3 GT 8/128 AZUL Y BLANCO
          $1’399.000🇨🇴
          MI 10T LITE 6/128GB AZUL
          $ 1.235.000🇨🇴
          MI 11 LITE 5G 6/128GB NEGRO
          $1’265.000🇨🇴

          Store E Example
          Note 10 pro 128gb 6ram $1.329.000🇨🇴 Azul
          Note 10 5G 128gb 4ram $949.900🇨🇴 Gris
          Poco x3 pro de 128gb 6ram $ 924.900🇨🇴 negro azul y bronce
          Poco x3 pro de 256GB 8ram $1.059.900🇨🇴 Negro y azul
          Poco x3 GT 128gb 8ram $1.249.900🇨🇴 azul negro y blanco

          There are like 15 stores sending prices over WhatsApp I would love to understand how to adjust the pattern in the most efficient way. I don´t have any problem cleaning the data before using the Regex Formula. I mean replacing... ( . , ’ ) all the characters between my prices.

  7. Daro says:

    Hello Mr.Alexander Could you help me please I have example as below

    LG-101+CC100+S+22 11 21+625+3
    LG-101+CC100+S+22 11 21+625+300
    LG-101+CC109+M+22 11 21+609+220
    LG-76-2+92C+S+22 11 21+618+1140
    and I want to get only value after + at the end of the cell like below
    3
    300
    220
    1140

  8. Zevi says:

    I'm trying to extract number and the text attached to it, lets say I have a list of items with different sizes as 200ml & 300ml, I want to extract 200ml 300ml from the cell, and also remove it from the source cell, is there any way with or without your tool to do this?
    Thank you very much!

  9. jp says:

    What went wrong here?

    A2: 10-Hour Orchid Class
    B2: =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&LEN(A2)+1)), 1) *1), 0) -1)

    Evaluation:
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&LEN(A2)+1)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&20+1)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:"&21)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT( "1:21")), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT($1:$21)), 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, 1, 1) *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER("1" *1), 0) -1)
    =LEFT(A2, MATCH(FALSE, ISNUMBER(1), 0) -1)
    =LEFT(A2, MATCH(FALSE, TRUE, 0) -1)
    =LEFT(A2,#N/A -1)
    =LEFT(A2,#N/A)
    =#N/A

  10. Jessie says:

    I would like to make a formula that can Extract the individual numbers from the example below. The numbers will change on either side of the x. My goal is to extract the single digits and place them in their own sell. The example below will be located in one cell group together again the digits will change depending on information.

    Example one .75 x 2 x 31
    Example two. 4 x 89 x 107

    .75 x 2 x 31 >>>> 0.75 2 31
    4 x 89 x 107 >>>> 4 89 107

  11. Sumit says:

    Hi All,

    I have a sentence in one cell and want to extract all the values in multiple columns e.g.

    Data in cellA1
    Total sales increased $58.9 billion or 4.5% from October 31, 2020 due increase in demand medical products by $15.2 billion or 2%

    I want all the Monetary values and % in different cells
    B1 = 58.9
    C1 = 4.5
    D1 = 15.2
    E1 = 2

  12. sunny says:

    I Textjoin row of cells. Only one contains date and time. Other cells, if NOT blank, contains text including numbers NOT dates.

    Textjoin works but date and time is now in serial format.

    How to convert that serial format within the Textjoin Output back to date and time?

  13. Debs says:

    Hi there,

    Is there one formula that can turn the below into "h:mm:ss"? The data source can be in below formats
    3h 31m
    8h
    51m 3s

  14. BT says:

    Hi!

    I am looking for a formula to return the first two digits of an account number

    ie account number1 =1212341234567000
    account number2 =0812341234567000

    I want to be able to return just 12 for account 1 and 08 for account 2. (I then want to assign a name for each of these first two digits).

    Please help :)

  15. Marisa says:

    Hello, thanks for your help! Can you please help with the following:
    Each row corresponds to one cell:

    53QBx13 bunches Limonium Piña Colada 70cm (10st) $2.60
    13QBx13 bunches Limonium Piña Colada 80cm (10st) $2.80
    8EBx10 bunches Limonium Oshi Pink BQT 60cm (20st) $3.50

    I need to extract in columns the following:
    Column 1
    53
    13
    8
    Column 2
    QB
    QB
    EB
    Column 3
    13
    13
    10
    Column 4
    Limonium Piña Colada
    Limonium Piña Colada
    Limonium Piña Colada
    Column 5
    70
    80
    60
    Column 6
    $2.60
    $2.80
    $3.50

    Is this possible?

    Thanks!

  16. Allison says:

    Hi Alexander! I just wanted to thank you so very much for your time and effort in assisting me.

    In case you were curious, I was able to extract and add the numbers that I wanted by using this VBA code along with "SUMNUMS":

    Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double
    Dim arr As Variant
    Dim xIndex As Long
    arr = Split(pWorkRng, xDelim)
    For xIndex = LBound(arr) To UBound(arr) Step 1
    SumNums = SumNums + VBA.Val(arr(xIndex))
    Next
    End Function

    Thanks again, you are amazing!

  17. Allison says:

    Good day! I am trying to extract and add the number of pages in each line of the following example:

    (1 x Stapled): 1 x Ldgr/C
    (1 x Stapled): 4 x Ltr/D
    (1 x Stapled): 17 x Ldgr/C
    (1 x Stapled): 26 x Ldgr/C/D; 1 x Ldgr/C
    (1 x Stapled): 16 x Ldgr/C/D; 1 x Ldgr/C
    (2 x Stapled): 32 x Ldgr/C/D; 2 x Ldgr/C
    (1 x Stapled): 9 x Ltr/D; 7 x Ltr/C/D; 1 x Ltr
    (1 x Stapled): 1 x Ltr/C

    Might you be able to assist with a formula? Much appreciated!

      • Allison says:

        Hi Alexander! Thanks so much for the quick reply. This is data from our photocopier usage. Each line is a print job; the first number is how many staples were used, followed by how many pieces of paper, letter (ltr) or ledger (ldgr))size. I'm trying to extract the number of pages printed for each line and add them together.

        I hope that helps! Please let me know if you need further details.

  18. Jacks says:

    5KM (1) Back to Basics
    >21KM Sky's the Limit

    Hi, I'm amazed by Sir Alexander's superb excel skills. I'm just trying out my luck here hopefully sir can solve my problem. I would like to extract only the number '5' and '21'. I wonder it is possible. Thank you.

    • Hello!
      Press CTRL + H. In the "Find what" field, write (*). Do not write anything in the "Replace with" field. Click "Replace".
      Then use the formula from this article and comments. For example:

      =TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))

      I hope I answered your question.

  19. Mike says:

    Hi,

    I have a spreadsheet of thousands in the following format
    1. zvsnsnshs 2020DDE542134
    2. sgenemene2020SHB6721
    3. reenmennee 2020RTY409

    I want to extract 2020DDE542134 in 1, 2020SHB6721 in 2 and 2020RTY409 in 3

    2020 is followed by three letters but the number of digits thereafter vary.

    Please assist.

  20. SJ says:

    I use this to get numbers only from mixed Alpha Numeric strings
    TEXTJOIN(“”,TRUE,IFERROR(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1,””))

  21. asif says:

    Hello,
    How can i remove last numeric digit from a text numeric string e.g.

    abc, adi, 23 fhve sihf ghr 98000

  22. Donald says:

    Thank you for this amazing formula!

    =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)

    It works almost perfectly, however I would like to separate the different number sets with a space.
    e.g.
    returned value with formula: 5468751013
    desired return value: 546875 1 0 13 (number sets vary)

    Regards
    Donald

  23. Wally says:

    I find this thread quite amazing.

    What I offer below may be a solution to a one-off problem as no-one else describes anything similar. But perhaps I am not the only one so here goes.

    When I scraped a table from the following site:

    https://en.wikipedia.org/wiki/List_of_countries_by_net_migration_rate

    - I found columns of numbers displayed in Excel as either regular positive numbers, aligned right, or negative numbers as text, aligned left. I tried all the techniques recommended for converting text to number but nothing worked. So in frustration I converted them all manually, hardly a practical solution for a large database.

    Then it dawned on me that it must be the actual "-" character which was causing the problem. I discovered there are online Unicode character identifiers, one of which I used to identify how these negative characters were different. The "-" for a regular negative number identified as "U+002D : HYPHEN-MINUS {hyphen or minus sign}". The problem character identified as "U+2212 : MINUS SIGN". When I used Ctrl-H, find and replace-all in Excel, all the negative text numbers instantly converted to regular numbers, aligned right, no further action needed.

    • Hi,
      Please check the formula below, it should work for you:

      =LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"), SEARCH("-",SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)), MID(A1,ROW($1:$94),1)," ")))," ","-"),1)-1)

      I hope it’ll be helpful.

  24. Andrew N says:

    Hi,

    I have a spreadsheet of a couple of thousand lot plans in the format of
    103SP122202
    10SP133260
    1RP43701
    They are always numbers followed by letters followed by numbers.
    I am looking for a formula to return all the numbers before the first letter and place in a column
    103
    10
    1
    Then a formula to return all the letters, and the numbers after the letters to place in another column
    SP122202
    SP133260
    1RP43701
    how would i achieve this?

    • Hello!
      Write your value in cell A1. To extract the text, write the formula in B1.

      =SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A1,ROW($1:$94),1))), MID(A1,ROW($1:$94),1),"")))," ","")

      To extract the first number, write the formula in C1.

      =LEFT(A1,SEARCH(B1,A1,1)-1)

      To extract the second number, write the formula in D1.

      =RIGHT(A1,LEN(A1)-SEARCH(B1,A1,1)-1)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  25. Joshua B says:

    I've been an advanced Excel user for 10+ years. It's pretty rare these days but when I hit a wall I eventually search for something online and I'm amazed, how many of those times I've ended up on one of Svetlana's posts, like 50+. I'm a big fan of hers! Joshua

  26. Rahul Tiwari says:

    Hi Team,

    I want below number to be extract from below given sentence.

    Q. 1 : "GL CASH DIPOSITED DONE BY ONE DATE IS 05/10/2020 02771600241 [AccountID: 123456767"

    I want the answer should be "02771600241"

    Q2: CASH DEPOSITED IN TRANSACTION ID - 02801900536 [AccountID: 1257895333 Account Name: Cas

    I want the answer should be " 02801900536 "

  27. Jessica says:

    What if I have multiple potential phone numbers in a free-form field and want them to extract, but be separated by a delimiter? Working from a DB extract where the most useful phone numbers are entered free-form with other miscellaneous tidbits like this:

    UserName 1234567890 Location 0987654321 OtherInfo
    OtherInfo 1234567890 Location
    M 1234567890 C 0987654321
    Location 1234567890 0987654321

    While your formula works great for extracting the numbers, it's resulting in strings like this now 12345678900987654321, which I would then need to split back up. Not all #s are 10 digits. Some have only 7 and others are international.

  28. Katy says:

    Hi,

    If i need to do the sum of the below 2 columns which has number and a percentage in same cell, how do i do this?
    Could you please help. Thanks a lot.

    4 (100%)
    5 (83%)

  29. Kiran Kumar says:

    Hi Alex,

    Problem description

    TXT_TXN_DESC Required field
    NEFT Cr-UTIB0000231-SIVA C-JANA SMALL FINANCE BANK-AXIR210011779565 AXIR210011779565
    RTGS Cr-HDFC0000240-LIGHTMICROFINANCEPVTLTD-JSFBCollectionAccountMSE-HDFCR52021010166833120 HDFCR52021010166833120
    NEFT SBIN521001912911-Mrs GOPA BHATACHARJEE-JANA SMALL FINANCE BANK LTD SBIN521001912911
    30768647394421 KKBKR52021010200888475 KKBKR52021010200888475
    33598650000698 20210102 IOBAN21002635205 IOBAN21002635205
    30098850001352 P002210081154581 20210102 P002210081154581

    What formula will work for to segregate an UTRNs to new column, please suggest.

  30. Matt says:

    Hi,
    I have used your formula above for extracting numbers from the left of a string [=LEFT(C738,SUM(LEN(C738)-LEN(SUBSTITUTE(C738,{"0","1","2","3","4","5","6","7","8","9"},""))))] but it is not returning the expected result:

    * String - 198503_NA_ST17 9UQ

    * Expected result - 198503

    * Actual result - 198503_NA

    If you could give me any indication as to where I have gone wrong it would be very much appreciated.
    Kind regards,
    Matt

      • Matt says:

        Hi Alexander,
        Thanks for your help but unfortunately that is returning #N/A.

        I changed the cell reference to C113 to suit where I am extracting the data from (I am extracting it into cell A113) and changed ROW references to $5:$475 as those are the rows my full data set sits in.

        Have I gone wrong somewhere making those changes? I tried it without changing the ROW references but it still returns #N/A.

        Thanks again for your help.
        Matt

  31. Josh says:

    Thanks for the site, I reference often.

    Here is my new hack for this: (OFFICE365 Only)

    =LET( A, MID($J2,ROW(INDIRECT("1:"&LEN($J2))),1),
    F, FILTER(A, ISNUMBER(A*1)),
    CONCAT(F)
    )

  32. Huzaifa says:

    Hello, plz
    Can anybody help me out.

    I have 26(4),5(7),9(10) in A1.

    I want to extract the numbers like this:

    26 in B1
    4 in C1
    5 in D1
    7 in E1
    9 in F1
    10 in G1

    Plz Help.

  33. ZAC says:

    Good day,

    what formula will work best, if you want to create a register list. That when you type an employee's pers number all the personal info pulls through eg. name, surname, job title and workplace. I have the master data sheet but want to make my life easier when reporting on other related reports.

  34. shay says:

    Hi, I used the formula to extract number from beginning of strings :

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

    to extract

    2A 1234521

    it was supposed to extract 2, but instead it extract

    2A 12345

    Why is that? Please help.

  35. Monica Ranjini says:

    Hello
    I would like to extract only those number which has tin written in front. can someone help me with formula.

    Goodman Fielder tin500766109 FOODMEA072 Chicken Thighs Normal (CTN/7.5KG)
    Foods Pacific Ltd tin 500546606 FOODDAI074 Cheese Mozzarella Grated 5 Star Gold (CTN/ 2x5KG)
    Tappoo tin500618105 BEVCIDER004 Cider Pear Isaac's (CTN/ 12x330ml)
    Tappoo tin500618105 BEVCIDER005 Cider Apple Isaac's (CTN/ 12x330ml)
    Satish Kumar Marketing tin 113065604 FOODVEG074 VegAlfalfa Sprout Imported (Punnet)

  36. Lee_ says:

    Hello Reader, just another comment . . .

    But need you + any assistants, to know VERY VERY sincere appreciation for such brilliant compilation of commitment to others having a success using sheets, over many years ! !

    I only started when PC’s were 16K / 64K we could do 255 x 255 single sheets . . . Oh, how it’s changed.

    THANKYOU & hopefully your future plans for site develop how you wish.

    Best Regards,
    Lee_ an Aussie

    ps. ;-)

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

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

    • Hello!
      You are using commas as word separators. You can extract the penultimate word using the formula —

      =TRIM(MID(A1,FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-1 -LEN(SUBSTITUTE(A1,",",""))),1)+1, FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)- LEN(SUBSTITUTE(A1,",",""))),1)- FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-1 -LEN(SUBSTITUTE(A1,",",""))),1)-1))

      Hope this is what you need.

  39. 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?

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

  41. 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)

  42. 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),"")

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

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

  45. Ashok says:

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

  46. 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?

  47. 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?

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

  49. Sichuan says:

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

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

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

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

          • Peter says:

            Hi Alexander, in my case I'd need it more dynamic. Raw data consists of multiple words and there are 2 numbers included. These numbers to be separated by a delimiter.
            Is it possible to create this formula without a specific pattern. That it simply extracts the numbers out of a text. no matter how long the text is and how long the numbers are. delimited by a special char such as blank or "-"? Thanks in advance for your help

            Example:
            client has ordered 500 pieces and wants to have 500 eur in return.
            desired result in cell with formula: 500-500

            client has exerciesed 500 and wants 3564656,32 new
            desired result in cell with formula: 500-3564656,32

            • Hi,
              If I get you right:
              To extract numbers "500-500" from text "client has ordered 500 pieces and wants to have 500 eur in return",
              use the formula

              =SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A2,ROW($1:$94),1)), MID(A2,ROW($1:$94),1)," ")))," ","-")

              I hope this will help

              • Peter says:

                Dear Alexander, thanks for your help on this. It works awesomly. Just had to perform SHIFT+STRG and ENTER in order to get the curly bracket around:-).Highly appreciated.

              • Peter says:

                Dear Alexander, I just wanted to know how the formula could be adpated in order show the below. So if the first and or second number is with mentioning ofcomma/point.

                as with previous forumla it shows as below when comma/point are inside the numbers:
                500-008-356456-32

                raw data:
                client has exerciesed 500,008 and wants 3564656,32 new items

                desired result in cell with formula:
                500,008-3564656,32

                raw data:
                client has exercesed 500.008 and wants 3564656.32 new

                desired result in cell with formula:
                500.008-3564656.32

                thanks in advance for your help.

              • Hello!
                You want to extract not only numbers, but also text. Comma and period are text. This cannot be done with a single formula. I was able to do this with Abledits Tools. First I used Convert Text (replace letters with spaces), then Trim Spaces (remove extra spaces) and again Convert Text (replace the space between numbers with a dash).
                You can install in a trial mode and check how it works for free
                If something is still unclear, please feel free to ask.
                You can ask a question on the blog or write to support@ablebits.com, include the link to your blog comment.

              • Hi Guru says:

                Thank you in Sharing your "Learned Wisdom "

                We learn Every Moment even after the Physical Invisibility of ourselves.

                What is the Purpose of Learning if we choose not to share.

                Educate a Man" and Thou will Feed the Nations of the World.

                Blessings to You

              • Peter says:

                Dear Alexander,

                thanks for your hint. Issue is, that on my machine at work I am not allowed to install anything due administrator.

                So I hasd to replace the dots and commas by nothing. Then your formula worked well!!

                Thanks for your hint with your tool. Wil use it at home:-).

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

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

  55. 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?

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

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

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

  59. vinay says:

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

  60. 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 :)

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

  62. Fahad Alkhaldi says:

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

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

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

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

  66. 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),"")

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

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

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

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

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

  72. 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),"")

  73. 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))

  74. 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),"")

  75. 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! :)

  76. 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)),"")

    • Pintu says:

      According to your para, find the number first, once you get all the numeric, then take the left 10 digits, using the left formula.

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

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

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

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 :)