What Is Extract Number From String Google Sheets?
Extract number from string in Google sheets, as the name suggests helps users extract the numbers from a given data string. It is highly useful when there are multiple data with mixed text and numeric values. We can extract number from string in Google sheets using various functions in Google sheets.
For example, consider the below table showing ID of an employee in column A.
Now, we need to extract the number from the data string in cell A1.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B2. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the REGEXEXTRACT function in Google sheets formula.
The complete formula is = REGEXEXTRACT(A2,”\d+”)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Likewise, we can use REGEXEXTRACT function to extract number from string in Google sheets. Remember, this is one of the many methods to extract number from string in Google sheets.
In this article, let us learn how to extract number form string in Google sheets with detailed examples.
Table of Contents
Key Takeaways
- Extract number from data string in Google sheets is used to extract the number from our data.
- We can extract numbers using functions such as LEFT, RIGHT, MID functions in Google sheets and Excel.
- In Google sheets, we can simply use REGEXEXTRACT function to extract numbers from data string.
- The syntax of REGEXEXTRACT function in Google sheets is
- =
REGEXEXTRACT(text, regular_expression) where both the arguments are mandatory.
- =
- Extracting is nothing but taking the numeric values from the mixed data string in cells in Google sheets.
How To Extract Number From String Google Sheets?
We can extract number from string using the below steps.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. Now, select the cell where we want to find the result.
Step 2: Insert the REGEXEXTRACT function in Google sheets formula.
Step 3: Press Enter key.
We will be able to see the result in cell B2.
Likewise, we can use REGEXEXTRACT function to extract number from string in Google sheets.
Similarly, we can use functions like RIGHT, LEFT and other functions in Google sheets to extract numbers from string in Google sheets.
Examples
Let us understand how to extract numbers from string in Google sheets with detailed following examples.
Example #1 – Extract Number From The String At The End In Google Sheets
For example, consider the below table showing data in column A.
As we can see, the data is mixed with name and age in column A. Now, we need to extract the age in number from the data string in cell A2.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B4. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the REGEXEXTRACT function in Google sheets formula.
The complete formula is = REGEXEXTRACT(A2,”\d+”)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Now, using AutoFill, we can extract number from other strings as shown in the below image.
Likewise, we can use REGEXEXTRACT function to extract number from string in Google sheets.
Example #2 – Extract Numbers From Right Side But Without Special Characters
For example, consider the below table showing admission number in column A.
Now, we need to extract the number from the data string, from the right side in cell A2.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B4. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the RIGHT function in Google sheets formula.
The complete formula is = RIGHT(A2,4)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Now, using AutoFill, we can extract number from other strings as shown in the below image.
Likewise, we can use RIGHT function to extract number from string from the right side in Google sheets.
Example #3 – Extract Number From Any Position In Google Sheets
Consider the below table showing admission number in column A.
Now, we need to extract the number from the data string, from any position in cell A2.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B4. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the LEFT function in Google sheets formula.
The complete formula is = LEFT(A2,4)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Now, using AutoFill, we can extract number from other strings as shown in the below image.
Likewise, we can use RIGHT function to extract number from string from the right side in Google sheets.
Example #4 – Extracting All Numbers In A String By Removing The Text Part
For example, consider the below table showing marks obtained by students in various subjects in column A.
As we can see, the data is not arranged properly and the numeric values are in different positions in each cell in column A.
Now, we need to extract the number from the data string in cell A2.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B4. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the following formula in Google sheets.
The complete formula is =SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(–MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1)) * ROW(INDIRECT(“1:”&LEN(A2))), 0), ROW(INDIRECT(“1:”&LEN(A2))))+1, 1) * 10^ROW(INDIRECT(“1:”&LEN(A2)))/10)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Now, using AutoFill option, we can find the result in other cells, as shown in the below image.
Likewise, we can use the combination of these functions in Google sheets to extract number from string in Google sheets.
Example #5 – Extracting Multiple Numbers From Different Parts Of A String Into Separate Columns
Now, let us use the same example, we used in Example 4.
For example, consider the below table showing marks obtained by students in various subjects in column A.
As we can see, the data is not arranged properly and the numeric values are in different positions in each cell in column A.
Now, we need to extract the number from the data string in cell A2 using REGEXTRACT function in Google sheets.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:C4. Now, select the cell where we want to find the result. In this example, it is cell C2.
Step 2: Insert the following formula in Google sheets.
The complete formula is =REGEXEXTRACT(A2,”\d+”)
Step 3: Press Enter key.
We will be able to see the result in cell C2, as shown in the below image.
Now, using AutoFill option, we can find the result in other cells, as shown in the below image.
Likewise, we can use REGEXEXTRACT function in Google sheets to extract number from string in multiple positions in Google sheets.
Important Things To Note
- Extract number from data string in Google sheets is used to extract numbers from cells.
- Users can extract numeric values from the data string in any position, even if the numeric values are in different positions.
- We can use the default functions to extract the numeric values in Google sheets.
Frequently Asked Questions
For example, consider the below table showing ID of an employee in column A.
Now, we need to extract the number from the data string in cell A1.
The steps are:
Step 1: To begin with, insert the data in the Google sheet. In this example, the data is in the cell range A1:B2. Now, select the cell where we want to find the result. In this example, it is cell B2.
Step 2: Insert the REGEXEXTRACT function in Google sheets formula.
The complete formula is = REGEXEXTRACT(A2,”\d+”)
Step 3: Press Enter key.
We will be able to see the result in cell B2, as shown in the below image.
Likewise, we can use REGEXEXTRACT function to extract number from string in Google sheets.
The syntax or formula of REGEXEXTRACT function in Google sheets is
=REGEXEXTRACT(text, regular_expression)
where,
text shows the cell or the cell range from which we want to extract the number.
regular_expression shows the first part which matches the expression that will be returned.
Both the arguments are mandatory
While using RIGHT function, the function extracts the number from the right. Similarly, while using LEFT, the function extracts the numeric value from the left or beginning of the string.
Download Template
This article must help understand Extract Number From String Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Extract Number From String Google Sheets. Here we explain how to extract a number from a string in Google Sheets with its examples. You can learn more from the following articles.
Leave a Reply