How to Extract Numbers from a String in Excel

If you are working with Excel, you know how challenging it can be to extract only numbers from a string consisting of text and numbers. This issue mainly arises when you need to perform mathematical operations on only the numerical data in your Excel sheet. Fortunately, Excel offers several built-in functions to extract numbers from a string, making it easier to work with numerical data in your spreadsheets. In this blog post, we will guide you through some of the most effective ways to extract numbers from a string in Excel.

Using LEFT and RIGHT Functions to Extract Numbers from Start/End of String

The LEFT and RIGHT functions are highly useful in extracting numbers that appear at the beginning or end of a string. For instance, if you have a string consisting of text followed by numbers, you can use the LEFT function to extract the numbers that precede the text. Alternatively, you can use the RIGHT function to extract the numbers that follow the text. The syntax of the function is as follows:

=LEFT(string, num_chars)

The above formula will extract the desired number of characters (num_chars) from the left side of the string.

=RIGHT(string, num_chars)

The above formula will extract the desired number of characters (num_chars) from the right side of the string.

Example: Using LEFT Function to Extract Numbers from Start of String

To extract numbers that appear at the beginning of a string, use the LEFT function. For instance, if you have a string consisting of text followed by numbers such as “Sales23”, use the following formula:

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

The above formula will extract the leftmost characters until a non-numeric character is found. As such, it will return “Sales” in the above example.

Example: Using RIGHT Function to Extract Numbers from End of String

In cases where numbers appear at the end of a string, you can use the RIGHT function. For instance, if you have a string consisting of text followed by numbers such as “Sales23”, use the following formula:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

The above formula will extract the rightmost characters until a non-numeric character is found. As such, it will return “23” in the above example.

Using Regular Expressions to Extract Numbers from Strings in Excel

If your data set is complex and contains numbers mixed into the text at various points, using regular expressions can come in handy. Regular expressions allow you to search for patterns within a string, which can be very powerful when searching for specific character types. Here is an example formula to extract all numbers from a string:

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

The above formula is essentially breaking down a string into individual characters and then checking whether each character is a digit or not. If it is, it returns it, and then adds the digits up. The resulting sum is all of the numbers found within the string.

Using Text to Columns to Extract Numbers in Excel

If you want a quick and easy way to extract numbers from a string in Excel, you can use the Text to Columns function. This function is designed to divide a selected cell into multiple cells based on a specific delimiter. To use this function to extract numbers from a string, follow the steps outlined below:

1. Select the cell that contains the string you wish to extract numbers from.
2. Click on the “Data” tab and then click on “Text to Columns”.
3. In the Text to Columns wizard, select “Delimited” and then click “Next”.
4. Select the delimiter that separates the text and numbers in your string (for example, a space or comma) and click “Next”.
5. Select the destination cell for the extracted numbers and click “Finish”.

Excel will then extract the numbers from the string and display them in the designated cell.

Filtering Non-Numeric Characters from a String in Excel

If you want to filter out non-numeric characters from a string in Excel, you can use a combination of TRIM, SUBSTITUTE, and REPT functions. The result of this formula will be solely numeric characters:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""))

The TRIM function removes extraneous spaces that may exist within the text. The SUBSTITUTE function replaces all instances of every non-numeric character (0-9) with a null string. The final result is a numeric string with all alphabet characters removed.

Using VBA to Extract Numbers from a String in Excel

If your Excel sheet contains a large amount of data that needs to be extracted, you may want to create a custom VBA function to simplify the process. You can declare an input string and use VBA’s RegEx Object Library to perform complex pattern matching for data extraction. The code below is an example of how to extract numbers using regular expressions in VBA:

Function ExtractNumbers(ByVal inputString As String) As String    Dim objRegEx As Object    Dim returnValue As String    Set objRegEx = CreateObject("VBscript.regexp")    objRegEx.Global = True    objRegEx.Pattern = "[^0-9]+"    returnValue = objRegEx.Replace(inputString, "")    ExtractNumbers = returnValueEnd Function

The code declares an input string, creates a Regex object, sets up the regex pattern, and then uses the Replace method to substitute non-numeric characters with a null string. This VBA code supersedes the need to use codes that were previously aligned with the principles of a regular formula.

Extracting numbers from a string in Excel can seem like a challenging task, but as outlined above, there are different formulas and techniques that you can use to extract data effectively. These formulas work with alphanumeric data, and they simplify the task of handling Excel data that has a complex composition. Improved knowledge of these functions can save precious productivity time and contributes to making data analysis a lot faster and easier.

FAQs

Here are some common questions people ask about extracting numbers from a string in Excel.

Can I extract numbers from strings in Excel without using complicated formulas?

Yes, you can extract numbers in Excel by using the Text to Columns function. The Text to Columns wizard can separate text and numbers in a selected cell using a specific delimiter, which you choose.

How can I extract numbers from the start of a string in Excel?

You can extract numbers that appear at the beginning of a string in Excel by using the LEFT function. This function returns the specified number of characters from the left side of the string.

I need to extract numbers from a string at various points within the string. Is there a formula I can use?

Yes, you can use a combination of SUMPRODUCT, MID, ROW, and ISNUMBER functions to extract numbers from strings that are located in various parts of a string. This formula makes uses of regular expressions to find and extract numeric characters.

Does Excel have a built-in function to filter out non-numeric characters from a string?

No. There is no built-in Excel function to remove non-numeric characters from a string. However, you can use a combination of TRIM, SUBSTITUTE, and REPT functions to remove non-numeric characters from a string. This function returns only numeric characters when executed on the data.

Can I use VBA to extract numbers from a string in Excel?

Yes, VBA allows you to create a custom function that extracts numbers from a string using the regex pattern. You can use VBA and create a custom function that accomplishes the same task as other Excel formulas have done before. This method is more efficient when working with significant sets of data.