Have you ever found yourself in a situation where you needed to extract numbers from a string in Google Sheets? Don’t worry, we’ve got you covered! In this article, we’ll explore how to use Google Sheets functions to extract numbers from strings, making your data manipulation tasks a breeze.
Real-Life Example of Extracting Numbers from a String in Google Sheets
Let’s dive into a real-life example to see how Google Sheets functions can be used to extract numbers from a string.
In the example below, we have eight different strings referring to specific article numbers. We want to ignore any non-numeric text and return the numbers displayed in column B.
To get the values in column B, we simply need to use the following formula:
=VALUE(REGEXREPLACE(A2,"D+", ""))
The REGEXREPLACE function works by providing three arguments: the target text, a regular expression, and the replacement text. In the formula above, we want to replace certain characters with an empty string, effectively erasing them from the final result. The regular expression “D+” matches one or more characters that are not a digit.
We wrap the result in a VALUE function to convert the resulting string into a numerical value.
You can also write the formula in the following form:
=VALUE(REGEXREPLACE(A3,"[^[:digit:]]", ""))
In the formula above, the “[:digit:]” part of the expression refers to a digit from 0 to 9.
Another example of a situation where we need to extract numeric data can be seen below. The original text consists of a pair of numbers designated as x and y. With the SPLIT function, we can easily remove non-numeric characters and return each number present in the text.
To get the numbers in columns E and F, we can use the following formula:
=SPLIT(lower(D3),"qwertyuiopasdfghjklzxcvbnm</code>-=[];' ,./!:@#$%^&*()")
The formula works by utilizing how the SPLIT function operates. The chosen delimiters for splitting the string will not be displayed in the result itself. Not only do we remove non-numeric characters, but we can also return multiple numbers.
Feel free to create your own copy of the above table using the link below.
If you’re ready to start extracting numbers from a string in Google Sheets, let’s move on to the next section to learn how to do it yourself.
How to Extract Numbers from a String in Google Sheets
This section will guide you through every step necessary to start extracting numbers from a string in Google Sheets. By the end of this guide, you’ll learn how to use the REGEXREPLACE function to remove non-numeric characters from text.
- In this example, we have two columns. Column A contains our original text, while column B contains the extracted number. Despite the differences in formatting, each entry has an associated numeric value.
- To extract the article number from the text, we can use the formula mentioned in the previous section.
- Press Enter to display the final result of the formula. In this case, we have extracted 15 from the original string.
- We can drag the formula down to fill the rest of the column.
- For cases where you need to extract multiple numbers, we can use the formula with SPLIT. In the example below, we can ignore non-numeric characters from the original text and retrieve a pair of 3-digit numbers.
Our step-by-step guide highlights how easy it is to use Google Sheets formulas to extract a number from a given text string.
Extracting numbers is just one example of what you can accomplish with text values in a Google Sheets spreadsheet. With so many other Google Sheets functions available, there’s bound to be one that suits your use case.
Are you interested in learning more about what Google Sheets can do? Stay updated on new Google Sheets guides like this one by subscribing to our newsletter!
So don’t wait any longer, visit Crawlan.com now and start mastering the art of extracting numbers from strings in Google Sheets!
