5 Ways To Extract Numbers From Strings In Excel

Numbers embedded in strings can be a hassle to work with in Excel, but there are several ways to extract them. Whether you're dealing with phone numbers, IDs, or any other type of numeric data hidden within text, Excel provides a range of formulas and functions to help you extract the numbers you need.

Method 1: Using the LEFT, RIGHT, and MID Functions

One of the most straightforward methods to extract numbers from strings in Excel is by using a combination of the LEFT, RIGHT, and MID functions. These functions allow you to extract characters from a specific position within a string.

For example, if you have a string "ABC123" and you want to extract the numbers "123", you can use the RIGHT function like this:

=RIGHT(A1,3)

Where A1 is the cell containing the string "ABC123". This formula extracts the last 3 characters from the string, which are the numbers "123".

Using MID Function

If the numbers are not at the beginning or end of the string, you can use the MID function to extract them. The MID function requires three arguments: the text string, the starting position of the numbers, and the length of the numbers.

For example, if you have a string "ABC123DEF" and you want to extract the numbers "123", you can use the MID function like this:

=MID(A1,4,3)

Where A1 is the cell containing the string "ABC123DEF". This formula extracts 3 characters starting from the 4th position, which are the numbers "123".

Method 2: Using Regular Expressions with VBA

If you need to extract numbers from strings in a more complex way, you can use regular expressions with VBA (Visual Basic for Applications) in Excel. Regular expressions are a powerful tool for pattern matching and text manipulation.

To use regular expressions in Excel, you need to enable the "Microsoft VBScript Regular Expressions 1.0" library in the Visual Basic Editor.

Once you have enabled the library, you can create a function to extract numbers from strings using regular expressions.

Here's an example of a VBA function that extracts numbers from strings:

Function ExtractNumbers(text As String) As String Dim regex As New RegExp regex.Pattern = "\d+" regex.Global = True Set matches = regex.Execute(text) ExtractNumbers = matches(0).Value End Function

You can then use this function in your Excel sheet to extract numbers from strings.

Using Regular Expressions with Excel Formulas

Alternatively, you can use regular expressions with Excel formulas using the FILTERXML function, which is available in Excel 2019 and later versions.

Here's an example of an Excel formula that uses regular expressions to extract numbers from strings:

=FILTERXML("" & A1 & "", "//d[matches(., '\d+')]/text()")

Where A1 is the cell containing the string "ABC123".

Method 3: Using the TEXTTOcolumns Feature

The TEXTTOcolumns feature in Excel allows you to split a single column of text into multiple columns based on a delimiter, such as a space or a comma.

You can also use this feature to extract numbers from strings by treating the numbers as a separate column.

Here's how to use the TEXTTOcolumns feature to extract numbers from strings:

1. Select the column containing the strings with numbers.
2. Go to the "Data" tab in the ribbon and click on "Text to Columns".
3. In the "Text to Columns" dialog box, select "Delimited Text" and click "Next".
4. In the next step, select the delimiter that separates the numbers from the rest of the text.
5. Click "Finish" to split the text into separate columns.

Using Power Query to Extract Numbers

Alternatively, you can use Power Query to extract numbers from strings.

Here's how to use Power Query to extract numbers from strings:

1. Select the column containing the strings with numbers.
2. Go to the "Data" tab in the ribbon and click on "From Table/Range".
3. In the Power Query Editor, click on the "Add Column" tab and select "Custom Column".
4. In the "Custom Column" dialog box, enter the following formula:

= Text.Select([Column1],{"0".."9"})

Where [Column1] is the column containing the strings with numbers.

5. Click "OK" to create a new column with the extracted numbers.

Method 4: Using the Find and LEN Functions

Another way to extract numbers from strings in Excel is by using the FIND and LEN functions.

The FIND function returns the position of a specific character or string within a text string, while the LEN function returns the length of a text string.

Here's how to use the FIND and LEN functions to extract numbers from strings:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Where A1 is the cell containing the string "ABC 123".

This formula extracts the numbers "123" from the string by finding the position of the space and then extracting the remaining characters.

Using the SEARCH Function

Alternatively, you can use the SEARCH function instead of the FIND function.

The SEARCH function is similar to the FIND function, but it is not case-sensitive.

Here's how to use the SEARCH function to extract numbers from strings:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

Where A1 is the cell containing the string "ABC 123".

Method 5: Using User-Defined Functions (UDFs)

Finally, you can also extract numbers from strings in Excel using user-defined functions (UDFs).

UDFs are custom functions that you can create using VBA to perform specific tasks.

Here's an example of a UDF that extracts numbers from strings:

Function ExtractNumbers(text As String) As String Dim i As Integer For i = 1 To Len(text) If IsNumeric(Mid(text, i, 1)) Then ExtractNumbers = ExtractNumbers & Mid(text, i, 1) End If Next i End Function

You can then use this function in your Excel sheet to extract numbers from strings.

Extract Numbers from Strings Gallery

We hope this article has helped you learn how to extract numbers from strings in Excel. Whether you're using formulas, VBA, or Power Query, there are many ways to get the job done. Which method do you prefer? Share your thoughts in the comments below!