Google Sheets: How to Extract Numbers from String


You can use the following formula to extract numbers from a string in Google Sheets:

=REGEXEXTRACT(A1,"-*\d*\.?\d+")

This formula will extract integers, decimals, and negative signs from a string.

The following example shows how to use this formula in practice.

Example: Extract Numbers from String in Google Sheets

Suppose we have the following list of strings in Google Sheets:

We can use the following formula to extract numbers (including integers, decimals, and negative signs) from the strings:

=REGEXEXTRACT(A2,"-*\d*\.?\d+")

The following screenshot shows how to use this formula in practice:

extract number from string in Google Sheets

Notice that the values in column B only contain the integers, decimals, and negative signs from the values in column A.

If you ever attempt to use this formula and you receive #VALUE! as a result, make sure that the strings are formatted as Plain text.

To convert the strings to Plain text, highlight the cell range that contains the strings, then click the Format tab along the top ribbon, then click Number and then click Plain text.

This should resolve any #VALUE! issues.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Extract Substring in Google Sheets
How to Substitute Multiple Values in Google Sheets
How to Count Cells with Text in Google Sheets

Featured Posts

One Reply to “Google Sheets: How to Extract Numbers from String”

  1. This is SO helpful! I’ve tried other extraction formulas on my dataset and run into issues with multi-digit numbers, but this works well. The only issue I’m running into is the #VALUE error which appears to be happening for cells that have only numbers to begin with. That is, the column I’m looking to extract the numbers from has some rows with other characters and some that are only numbers. When I drag down the formula, the #VALUE error is appearing next to these already number-only fields. I’ve made sure the source column is plain text per your note but still running into this issue. Any ideas?

Leave a Reply

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