Mehrab Imtiaz
In this article, we will show how to convert a text value with a number value in Google Sheets.
Knowing how a value is formatted is quite important when working with a spreadsheet.
Consider a scenario where the values are formatted as text. If we are to use them in a numerical formula, it will most likely create some complications.
So, it can be a good idea to convert the text values to numbers first before we attempt any numerical calculations.
And there are multiple ways to do so, each depending on their unique scenarios
How do I Recognize Whether a Value is a Text or Number in Google Sheets?
We can quite easily recognize whether a value is a number or a text by seeing its alignment in the cell.
The values that are left-aligned are text and the values that are right-aligned are numbers.
However, the value alignment of a cell is not always reliable. What if the cell was formatted with a different alignment?
Another way to check whether a value is a text or not is by using the ISTEXT function. It gives a Boolean output depending on the value. We get TRUE if the value is a text, otherwise, we get a FALSE.
On the flip side, we have the ISNUMBER function. It works the same way as ISTEXT, but for numbers.
Recognizing which values are text is the first step to converting text to numbers in Google Sheets.
6 Scenarios and Methods to Convert Text to Number in Google Sheets
1. Convert to a Number Using Number Formatting
For our first scenario, we will continue from our previous dataset where we had a few numbers that are actually text values as proven by the ISTEXT function.
As for our conversion method, we will simply use a built-in option of number formatting available to us in Google Sheets.
Simply select the text values that you want to convert and navigate to the More Formats tab in the toolbar. Here you will find the Number format option.
Clicking on the Number option will immediately convert the selected text to a number in Google Sheets. Notice also the change of the values of the ISTEXT formula in the adjacent column.
2. Using Multiplication to Convert Text to Number in Google Sheets
Another simple way to convert numbers as text to number values is to use multiplication.
Google Sheets is smart enough to recognize the underlying values in a cell. So, when we multiply a number in a text format, we get a numerical result.
Simply multiply the value by 1:
=B1*1
Since it is simple multiplication, there are multiple formulas we can apply:
=MULTIPLY(B1,1)=ARRAYFORMULA(B1:B5*1)=ARRAYFORMULA(MULTIPLY(B1:B5,1))We suggest you use the ARRAYFORMULA variants when you have a large amount of adjacent data to convert. This will save a lot of processing time.
3. Using the VALUE Function to Convert Text to Number in Google Sheets
Another way to take advantage of Google Sheets’ value recognition is by using the VALUE function to convert text to numbers.
Simply reference the cell that contains the number value as text within the VALUE function to convert it:
=VALUE(B1)
There are a couple of other functions that come from VALUE that are more specialized to convert different types of values to numbers.
The TIMEVALUE function converts time values of either text or number formats to their underlying numerical value.
TIMEVALUE(time_string)The DATEVALUE function does the same, but for dates.
DATEVALUE(date_string)All the VALUE functions in action:
4. Replacing Dot Separators with Comma to Convert Text to Number
Some number formats may use dot/period (.) separators instead of comma thousands separators in Google Sheets.
For example, writing 12.000 instead of 12,000 where the dot is not a decimal point.
It is mostly due to regional or preferential differences than anything else.
We must first replace the dot with a comma to convert this number. We can do this by using the tried-and-true REGEXREPLACE function:
=REGEXREPLACE(B1,"[.]",",")
The replaced result is still in the text format. However, this time, we can convert this text using any of the methods we’ve discussed above.
Here’s the difference between doing a numerical calculation between a text number with a dot separator and a text number with a comma separator:
The output by the comma separator value gives us not only the correct result but also a number value (right-aligned).
5. Convert Currency to Raw Number Values
The currency is a different type of value format. While it acts as a number when used for calculations, the resultant values will also be in the currency format.
Another time where the currency symbol, namely the Dollar ($), may appear is after a data import. The $ symbol has a lot of significance in spreadsheets.
For either of these cases, we may want to retrieve only the base numerical values, in other words, convert them to numbers.
For that, we have the TO_PURE_NUMBER function:
=TO_PURE_NUMBER(B2)
6. Extract Number Values from Text or String
Here are a few string values with numbers in them:
What we want to do here is extract the numbers from the string separately, leaving only numerical values.
The simplest way to do this is by using the REGEXREPLACE function.
The function will use regular expressions to find and extract the number values present in the string.
The formula:
What we get is a numerical value in the text format (left-aligned).
We can now convert it into a number using any of the simpler methods.
We’ve opted for the multiplication method:
=REGEXREPLACE(A2,"[^\d\.]+","")*1
Final Words
That concludes all the ways we can use to convert text to a number in Google Sheets.
Most of these methods are dependent on the scenario. So, it is crucial for a user to recognize the state of the worksheet to determine which method to use.
Feel free to leave any queries or advice you might have in the comments section below.
