What is Convert Text to Numbers in Google Sheets?
Understanding how to convert text to numbers in Google Sheets is crucial in data manipulation. It isn’t a very complex process, and there are many ways to do it. Some of the main methods used to convert text to numbers include using the VALUE function, formatting cells to convert text to numbers, using the ARRAYFORMULA and so on. In the upcoming sections, we will go through the different methods to transform the text into a number to help you understand the topic in detail.
In the example below, we use the VALUE function in Google Sheets to convert text-formatted numbers into numerical values. Initially, the value in cell A2 is text, as shown by the result of the ISTEXT function in B2. However, we convert the text into a number in A3 using the VALUE function. When we test the function using ISTEXT, it shows FALSE in B3.
Key Takeaways
- Converting text to numbers allows us to perform accurate data analysis. There are several ways to convert text in numerical form to numbers.
- The VALUE function converts a text string in a number format into a numerical value. Its syntax is: =VALUE(text).
- One can also convert text to numbers using the Format menu. To do so, navigate to the Format menu on the Google toolbar and choose Number to apply number formatting.
- Several advanced functions offer precision in the conversion of text to numbers. They include REGEXREPLACE and ARRAYFORMULA.
How To Identify Numbers Formatted as Text in Google Sheets?
As anyone with an iota of knowledge on Google Sheets can see, it stores data in various formats. The most common are text and number formats. We may often have to perform calculations on the numbers in the sheet, and they may sometimes be stored as text, especially when obtained from an external source. To ensure accurate results, we ensure we know that the data is a number before calculations.
#1 – Using SUM Function.
Let us see how to do it first with the SUM function.
We have a list of numbers. To understand how it works, we have them as text.
Step 1: Type the =SUM( formula in a cell
Step 2: Select the cell or range of cells. Close the parentheses and press Enter. If the formula returns a numeric result, the values are numbers. However, if they return a 0, they are text.
Step 3: Press Enter.
#2 – Using ISTEXT and ISNUMBER
Another way to identify numbers formatted as text is to use some helpful logical functions. You can also tell if a number is text using either the ISTEXT or the ISNUMBER functions.
ISTEXT is used to verify that a value is a text. For example, we check if the value in cell C2 is a text using:
=ISTEXT(C2)
We can also use ISNUMBER to verify that a value is a number.
Now, let us try the same with the ISNUMBER function.
Enter =ISNUMBER(C2) in a cell and check the result. It comes back TRUE, as we earlier checked that the number is not a text.
We use these two options when we have a combination of numbers and text.
Examples
When working with data in Google Sheets, converting text to numbers can often be required to perform quantitative analysis. Many times, the values in your sheet may appear to be numbers, whereas in reality, they are text. It is important to identify this before using them in calculations. There are ways you can check to see if a number is text.
Let us look at some interesting examples below.
Example #1 – Using the VALUE Function to Convert Text to Numbers
In the examples below, we will try a few formulas you can use to convert text to numbers in Google Sheets. The easiest way to do so is by using the VALUE function
Step 1: Enter some data in a sheet. One of the clues that the number may actually be a text is through the left alignment. However, we will continue using the VALUE function.
Enter the following function in cell B2.
=VALUE(A2). Press Enter.
It converts a text to a number, which can be verified by operating with it.
Step 2: Drag the formula to the other number as well. The default right alignment shows that it is now a number.
Example #2 – Formatting Cells to Convert Text to Numbers
Now, here’s an example on how to convert text to numbers in Google Sheets using the formatting option.
Step 1: Look at the data below. We have three numbers. To prove they are text, we have tried the SUM function on them and get the result as zero.
Step 2: Select the text data in the numerical format from A2 to A4. Go to Format > Number
Step 3: Select the option Number.
Once you’re done, your text will be converted to numbers. As soon as you use formatting, the alignment may change as Google Sheets are in the right alignment by default. You can also test this by observing the result of SUM.
Example #3 – Using REGEXREPLACE to Convert Text to Numbers
The above methods are straightforward and easy to execute. However, some advanced functions, like REGEXREPLACE and ARRAYFORMULA, can be used when handling the complex conversion of text to numbers in Google Sheets.
The REGEXREPLACE function replaces a part of a text string with a different text with the help of regular expressions. It helps you to work with text using regular expressions.
Its syntax is as shown: REGEXREPLACE(text, regular_expression, replacement)
Step 1: For example, we have a value “Rating: 4.5”, in cell B1, you can use the formula =VALUE(REGEXREPLACE(A1,”[^\d.]”,””)).
Press Enter.
The REGEXREPLACE function helps extract the rating and convert it to a number using the VALUE function.
This formula removes non-numeric characters and converts the remaining string to a number using the VALUE function.
Example #4 – Using ARRAYFORMULA to Convert Text to Numbers
The ARRAYFORMULA function in Google Sheets lets you apply a formula to multiple cells or ranges simultaneously. This feature is useful for analyzing and managing complex data.
The syntax of the function is as follows:
=ARRAYFORMULA(array_formula)
array_formula – A range on which any mathematical expression is applied that returns a result for more than one cell.
Therefore, instead of applying a formula to individual cells one at a time, we use ARRAYFORMULA to apply them to entire ranges simultaneously.
Step 1: Take a data set in column A that contains text that should be converted to numbers.
Step 2: To convert them to text, we enter the formula
=ARRAYFORMULA(VALUE(A2:A4))
It will convert all text values in cells A2 through A4 to numbers.
It should be noted that VALUE will only work with those strings that are in a numeric format and does not work with anything that is a string.
Important Things to Note
- Please note that VALUE works only with strings that are already in the form of a number but are left-aligned because of the text-value data format. If the string is not a numeric pattern, additional functions may be required.
- Using multiplication, you can convert a text into a numeric value. Use a formula such as A1*1 to multiply the text by one. The resultant value will become a numeric value.
- One might encounter the #VALUE! Error when the VALUE function cannot interpret the text as a proper number. In such cases, we can use the REGEXREPLACE function to extract the numeric characters from the text.
Frequently Asked Questions (FAQs)
Some of the reasons we may get errors when using Google Sheets to convert text to numbers include:
The string may contain extra spaces or invisible characters. We can use the TRIM function to remove all leading and trailing spaces.
=VALUE(TRIM(A1))
There may be some formatting issues. Hence, the cell containing the text must be formatted as “Number” or “Automatic.”
Dates and times are some other commonly used data types in Google Sheets. If a date is stored as text, it can be converted to a number using DATEVALUE() if it is a date and TIMEVALUE() if it is time. For example:
=VALUE(DATEVALUE(A1)).
After text is converted to numbers using functions such as VALUE or REGEXEXTRACT, any text-based numerical values that were initially aligned to the left will automatically align to the cell’s right side. It shows that the text has been successfully converted into numbers.
You can also use the ISTEXT or ISNUMBER functions to verify the same.
If the text remains left-aligned, it is not converted, which means the conversion was unsuccessful.
Download Template
This article must help understand Convert Text to Numbers in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Convert Text to Numbers in Google Sheets. We learn how to identify numbers formatted as text and convert them to text. You can learn more from the following articles. –
Subtraction Formula in Google Sheets
Leave a Reply