How to Extract Number from String in Excel [8 Methods]

ByRhidi Barma Updated on Basic Excel

Symbols, numeric numbers, and alphabetic characters are frequently mixed in strings. It is essential to separate these numerical elements from the surrounding language since doing so helps with calculations, reporting, and visualization—all activities that involve data. Analysts can execute mathematical operations, produce statistical insights, and create educational visualizations by extracting and converting numbers from strings, which helps to inform decision-making processes. In this article, I will show you how to extract numbers from a string in Excel.

Use Flash Fill to Extract Number from String in Excel

Microsoft Excel has a robust tool for manipulating data called Flash Fill. It is the simplest method for Excel to extract numbers from strings. However, only Microsoft Excel 2013 and later versions include Flash Fill capability.

Consider a Dataset that has a string of integers with the numbers at the beginning, as shown in the illustration below. Let’s extract them by making use of the Flash Fill function.

Dataset for Flash fill

The Flash Fill method can be used to extract numbers from strings. The process is given below:

  1. Choose cell (D2) and manually enter 8956.Manually typing for Flash fill
  2. Then pick cell (D3).
  3. Go to Data.
  4. Select Flash Fill from the Data Tools group. Or, click CTRL+E.

Applying Flash Fill using the Flash Fill option

Flash Fill allows us to retrieve numbers from the strings.

The final result after Flash Fill.

Using RIGHT, MIN, SEARCH Functions to Extract Number from String in Excel

The numbers or digits from the right side of the text string will be extracted in this part. Here, we’ll employ the RIGHT, MIN, and SEARCH commands. The dataset for this example is given below:

6 Data set for Right min search function for extract digit from right side of the string

Syntax

=RIGHT(text,LEN(text) - MIN(SEARCH({number 1,[number 2],…}, text&"within_text]")) +1)

Formula

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

Formula Breakdown

This formula is designed to extract the rightmost numerical portion from a cell (C2) containing alphanumeric characters.

  • It does so by first concatenating the cell content with the string “0123456789” and then searching for the positions of numeric characters (0-9).
  • The MIN function identifies the smallest position of any numeric character, and the overall length of the cell content (LEN(C2)) is subtracted by this minimum position.
  • Finally, the RIGHT function extracts the portion from the original cell starting from this calculated position, effectively isolating the rightmost numerical sequence in the alphanumeric string.

To extract numbers from strings, you can use the combination of RIGHT, MIN, and SEARCH functions. Follow the steps below:

  1. Copy the formula.
  2. Utilize the equation in cell D2.Combination of RIGHT MIN and SEARCH functions for extract digit from right side of the string
  3. Drag the Fill Handle from D2 to D10.Dragging the combination of RIGHT MIN and SEARCH function to extract digits from the right side of the string

Final Result

Consequently, every number from the string has been extracted.

Final result for the combination of RIGHT MIN and SEARCH function final for extract digit from right side of the string

Using ISNUMBER, MIN, SEARCH, RIGHT & LEN Functions to Extract Number from String in Excel

To extract the numbers from a text in a cell in Excel, there is no built-in function. But we can use the combination of these functions, ISNUMBER, MIN, SEARCH, RIGHT & LEN to Extract Numbers from String in Excel.

The previous has a slight issue because it doesn’t account for the possibility that there might be no numbers in the text. But this formula fixes these problems. The Dataset of this type of example is given below.

Dataset of ISNUMBER MIN SEARCH function for extracting digit from string

Syntax

=IF(ISNUMBER(MIN(SEARCH({number 1,[number 2],…}, text&"[within_text]"))), RIGHT(text, LEN(text) - MIN(SEARCH({number 1,[number 2],…}, text&"[within_text]")) + 1), "")

Formula

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

Formula Breakdown

It is a conditional statement that checks if a cell (C2) contains any numeric characters (0-9).

  • The SEARCH function identifies the positions of numeric characters after concatenating the cell content with the string “0123456789”.
  • The MIN function finds the smallest position, and the ISNUMBER function checks if there is at least one numeric character.
  • If true, the RIGHT function extracts the rightmost numerical sequence from the cell; otherwise, it returns an empty string (“).

This formula is useful for extracting numeric values from alphanumeric strings, and it ensures that only cells containing numeric characters receive a result, while others display an empty string.

You can use the ISNUMBER, MIN, SEARCH, RIGHT, and LEN functions to extract a number from a string. Take the actions listed below:

  1. Copy the formula.
  2. Apply the formula in cell D2.Isnumber min search function for extracting digit from string
  3. Drag the Fill Handle from D2 to D10.ISNUMBER MIN SEARCH function dragging for extracting digit from string

Final Result

Here, we see all numbers from the string are extracted. We also see that in Cell C7 there is no number present, so cell D7 is blank.

The final result of the ISNUMBER and MIN search function for extracting digits from a string

Applying LEFT, SUM, LEN & SUBSTITUTE Functions to Extract Number from String in Excel

To extract numbers from the start of a text string using this technique, we will combine the LEFT, SUM, LEN, and SUBSTITUTE functions. The Dataset of this type of example is given below.

Dataset of left len sum substitute function for extracting digit from string

Syntax

=LEFT(text,SUM(LEN(text)-LEN(SUBSTITUTE(text,{number 1,[number 2],....},""))))

Formula

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

Formula Breakdown

This is designed to extract the leftmost portion of a cell (C2) that consists of consecutive numeric characters (0-9).

  • The SUBSTITUTE function is used to remove all non-numeric characters from the original cell content by substituting them with an empty string.
  • The difference in length between the original content (LEN(C2)) and the content with non-numeric characters removed (LEN(SUBSTITUTE(C2,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))) represents the count of numeric characters in the cell.
  • The SUM function then adds up these counts, and the LEFT function extracts that number of characters from the left side of the original cell content, effectively isolating the leftmost numerical sequence in the alphanumeric string.

To extract numbers from strings by applying the combination of LEFT, SUM, LEN, and SUBSTITUTE functions. Follow the steps below:

  1. The above formula should be copied.
  2. Apply the formula in cell D2.left len sum substitute function for extracting digit from string
  3. Drag the Fill Handle from D2 to D10.left len sum substitute function dragging for extracting digit from string

Final Result

Every single integer in the string has been extracted as a result.

left len sum substitute function final result for extracting digit from string

Using TEXTJOIN, IFERROR, and INDIRECT Functions to Extract Numbers from String in Excel

This approach can extract integers or digits from any location within a text string. The TEXTJOIN, IFERROR, INDIRECT, MID, and ROW functions will also be used in this procedure. The Dataset of this example is given below.

Dataset of TEXTJOIN function for extracting digit from string

Syntax

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

Formula

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

Formula Breakdown

This formula is designed to concatenate all numeric characters from a given cell (C2) into a single text string.

  • The MID function is used to extract individual characters from the cell by iterating through each character position, and ROW(INDIRECT(“1:”&LEN(C2))) generates an array of sequential numbers from 1 to the length of the cell content.
  • Multiplying MID(C2,ROW(INDIRECT(“1:”&LEN(C2))),1) by 1 converts the extracted characters to numeric values while treating non-numeric characters as errors.
  • The IFERROR function is then employed to replace errors with an empty string.
  • Finally, TEXTJOIN(“”, TRUE, …) combines the resulting array of numeric characters into a single text string with no delimiter, effectively creating a string comprising only the numeric values from the original cell.

Using the TEXTJOIN, IFERROR, and INDEX functions in combination will allow you to extract numbers from strings. Take the actions listed below:

  1. Copy the formula above.
  2. Apply the formula in cell D2.Textjoin function for extracting digit from string
  3. Drag the Fill Handle from D2 to D10.Textjoin function dragging for extracting digit from string

Final Result

Here we can see that, after applying these functions we can extract numbers from any point of the string.

Textjoin function final result for extracting digit from string

Applying CONCAT and SEQUENCE Functions to Extract Number from String in Excel

Another Excel formula will be used to extract numbers from any location inside a string. In this section, we’ll utilize the CONCAT and SEQUENCE functions for the first time. The dataset is given below.

Dataset of concat function for extracting digit from string

Syntax

=CONCAT(IF(ISNUMBER(--MID(text, SEQUENCE(LEN(text)), 1)), MID(text, SEQUENCE(LEN(text)), 1), ""))

Formula

=CONCAT(IF(ISNUMBER(--MID(C2, SEQUENCE(LEN(C2)), 1)), MID(C2, SEQUENCE(LEN(C2)), 1), ""))

Formula Breakdown

It is designed to extract and concatenate all numeric characters from a given cell (C2) into a single string.

  • The MID function is used to extract individual characters from the cell by iterating through each position, where SEQUENCE(LEN(C2)) generates an array of sequential numbers corresponding to the length of the cell content.
  • The –MID(…) part attempts to convert each extracted character to a number; if successful (ISNUMBER(…) is true), the numeric character is included in the result; otherwise, an empty string is added.
  • The final result is a concatenated string containing only the numeric values from the original cell, with non-numeric characters omitted.

Using the CONCAT and SEQUENCE functions to extract a number from a string. Observe the following actions:

  1. Copy the formula above.
  2. Apply the formula in cell D2.Using the CONCATENATE and SEQUENCE functions to extract digits from a string.
  3. Drag the Fill Handle from D2 to D10.Dragging the CONCATENATE and SEQUENCE functions to extract digits from a string.

Final Result

After applying CONCAT and SEQUENCE functions, we can extract numbers from anywhere on the string.

The final result of using the CONCATENATE and SEQUENCE functions to extract digits from a string

Applying VBA Code to Extract Number from String in Excel

Utilizing VBA code is a different method for extracting numbers from strings in Excel. Using the programming language VBA (Visual Basic for Applications) in Microsoft Excel, you may automate processes, develop unique functions, and create applications. The dataset of this example is given below.

Dataset of VBA for extracting digit from string

Follow these steps below to apply VBA code to extract numbers from strings:

    1. First, choose cell C2:C10 and then press CTRL+C.
    2. Then choose D2:D10 and then press CTRL+V.CTRLC+CTRLV of VBA for extracting digit from string
    3. Go Press ALT+F11. This will open the Visual Basic Editor.
    4. Next, choose Insert to create a Module.Inserting a VBA code module for extracting digits from a string
    5. To add the code to the newly formed Module, copy and paste the following.
      Sub ExtractNumbersFromRange()
            Dim selectedRange As Range
            Dim cell As Range
            Dim result As String

            ' Prompt the user to select a range
            On Error Resume Next
            Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
            On Error GoTo 0

            ' Exit if the user cancels the selection
            If selectedRange Is Nothing Then
            Exit Sub
            End If

            ' Loop through each cell in the selected range
            For Each cell In selectedRange
            result = ExtractNumbersFromString(cell.Value)
            ' Replace the cell value with the extracted numbers
            cell.Value = result
            Next cell
End Sub

Function ExtractNumbersFromString(inputString As String) As String
            Dim i As Integer
            Dim resultString As String
            Dim char As String
   
            ' Initialize the result string
            resultString = ""

            ' Loop through each character in the input string
            For i = 1 To Len(inputString)
            char = Mid(inputString, i, 1)
            
            ' Check if the character is a digit (0-9) or a period (.)
            If IsNumeric(char) Or char = "." Then
            resultString = resultString & char
            End If
            Next i
            ' Return the extracted numbers as a string
            ExtractNumbersFromString = resultString

End Function
    6. Save the code by pressing CTRL+S.
    7. Go to Developer and click the Macros button or you can easily open the Macro dialog box by pressing ALT+F8.VBA code for extracting digits from a string using macros
    8. Click Run by selecting ExtractNumberFromRange.Using a VBA code macro in the Excel window to extract digits from a string
    9. Select the range D2:D10 and click OK.VBA Range selection for extracting digit from string

Applying Multiple Functions to Extract Numbers from String in Excel

We can use multiple built-in functions like TEXJOIN, ISNUMBER, MID, ROW, and INDIRECT to extract numbers from strings.

Dataset of multiple functions for extracting digit from string

Syntax

=TEXTJOIN("", value, IF(ISNUMBER(--MID(text, ROW(INDIRECT("1:"&LEN(text))), 1)), MID(text, ROW(INDIRECT("1:"&LEN(text))), 1), ""))

Formula

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(C2, ROW(INDIRECT("1:"&LEN(C2))), 1)), MID(C2, ROW(INDIRECT("1:"&LEN(C2))), 1), ""))

Formula Breakdown

It is designed to extract and concatenate all numeric characters from a given cell (C2) into a single string.

  • It utilizes the MID function to extract individual characters by iterating through each position, where ROW(INDIRECT(“1:”&LEN(C2))) generates an array of sequential numbers corresponding to the length of the cell content.
  • The –MID(…) part attempts to convert each extracted character to a number; if successful (ISNUMBER(…) is true), the numeric character is included in the result; otherwise, an empty string is added.
  • Finally, TEXTJOIN(“”, TRUE, …) combines the resulting array of numeric characters into a single text string with no delimiter, effectively creating a string comprising only the numeric values from the original cell while excluding non-numeric characters.

Follow  these steps below to extract numbers from string using multiple functions:

  1. Copy the formula above.
  2. Apply the formula in cell D2.multiple functions for extracting digit from string
  3. Drag the Fill Handle from D2 to D10.Dragging and utilizing multiple functions for extracting digits from a string.

Final Result

As a result, all numbers from the string have been extracted.

The final output from using multiple functions to extract digits from a string.

Conclusion

We have shown the flexibility of ways available for string-to-number extraction through the methods covered in this article, including manual methods, Excel functions, programming languages, and regular expressions.

Frequently Asked Questions

How do I extract only numbers from a string?

To extract only numbers from a string in Excel, you can use the following formula:=TEXTJOIN(“”, TRUE, IF(ISNUMBER(–MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1)), MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1), “”))

Replace “A1” with the cell reference containing your string.

This formula utilizes MID, ROW, INDIRECT, ISNUMBER, and TEXTJOIN functions to iterate through each character in the string, identify numeric characters, and concatenate them into a single text string.

The result is a string containing only the numeric values from the original cell, with non-numeric characters excluded.

How do I separate a number from a string in Excel?

Separating Text from Numbers in Excel (Formula):

  1. To extract text: =SUBSTITUTE(A1, TEXTJOIN(“”, TRUE, IF(ISNUMBER(–MID(A1, ROW(INDIRECT(“1:” & LEN(A1))), 1)), MID(A1, ROW(INDIRECT(“1:” & LEN(A1))), 1), “”)), “”).
  2. Replace “A1” with the reference to the cell containing your original string.

How do you split a string and number?

Splitting String and Number in Excel (Formula):

  1. To extract numbers: =SUMPRODUCT(MID(0&B1,LARGE(INDEX(ISNUMBER(–MID(B1,ROW($1:$300),1))*ROW($1:$300),0),ROW($1:$300))+1,1)*10^(ROW($1:$300)-1)).
  2. Replace “B1” with the reference to the cell containing your string.
  3. To extract text: =SUBSTITUTE(B1, TEXTJOIN(“”, TRUE, IF(ISNUMBER(–MID(B1, ROW(INDIRECT(“1:” & LEN(B1))), 1)), MID(B1, ROW(INDIRECT(“1:” & LEN(B1))), 1), “”)), “”).
  4. Replace “B1” with the reference to the cell containing your original string.

How do you convert a part of a string to a number?

To convert a part of a string to a number in Excel, you can use the following formula:=VALUE(SUBSTITUTE(MID(A1, start_position, length), “,”, “”))

Replace “A1” with the reference to the cell containing your string. Adjust “start_position” to the character position where your number starts, and “length” to the number of characters in the numeric part.

The SUBSTITUTE function is optional and is used to handle cases where the number might contain commas, removing them to ensure accurate conversion. This formula is useful for extracting and converting specific numeric portions from mixed strings in Excel.

Rate this post

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *