Mehrab Imtiaz
This article will look at how to convert any value to a number in Google Sheets.
There are many ways to do so, from formatting the cell to utilizing functions like VALUE among others. Then the question may arise, why do we need to do so?
Some spreadsheet values, especially when importing and exporting, may change their formatting.
If this happens to numerical values, then using them from calculations may give rise to complications.
That’s why it is sometimes good to convert values to their base number form before any calculations are applied.
3 Ways to Convert a Value to a Number in Google Sheets
1. Changing the Formatting of the Cell to a Number Format
Consider the following set of data:
Here we have a mixture of values that includes integers, date, time, text, and string. By default, numerical values in Google Sheets are right-aligned in a cell, whereas text values are left-aligned. This is the primary distinction between the two value types.
Now, let’s convert all these values to a number and see what happens.
The default way to do so is by using the Format menu.
First, select the range of cells you want to convert. Then navigate to the Number option from the Number submenu under the Format menu:
Format > Number > Number
Alternatively, you can also get the Number option from the More formats (the 123 icon) menu in the toolbar:
More formats > Number
Clicking on the Number option will convert the selected cells to the number format in Google Sheets.
As you can see, most of the values were converted to the previewed number format with two decimal places, while some remained unchanged. Why is that?
The values that were converted are the ones that are already recognized as numbers by Google Sheets (right-aligned) or the ones that have underlying numerical values even if they are in a different format (the left-aligned number, time, and date values).
2. Use the VALUE function to Convert to a Number without Changing the Source Data
A situational disadvantage of converting using the Format menu is that it will overwrite the source data with the formatted values.
But if you want to keep the source data untouched, the best idea is to use functions to convert the values. In this case, it is the VALUE function.
VALUE(text)
The way this function works is quite simple: any data that is passed through it will have its base number value extracted.
Using the VALUE function on the dataset:
=VALUE(B2)
As you can see, the number values, no matter the form, have been extracted as their base values. Whereas the pure texts or strings gave an error.
The VALUE function can only present numbers.
Tip: If you have a large amount of data, it is always a good idea to employ the ARRAYFORMULA function.
This will allow you to enter a range of cells in a function that was otherwise impossible and present the array of results accordingly.
Not only that, the ARRAYFORMULA function allows users to enter only one function for a large range of data which makes the processing speed of the application much more efficient.
The formula with ARRAYFORMULA and VALUE:
=ArrayFormula(VALUE(B2:B7))
Note that the VALUE function is quite generalized. It can work with any type of number value.
To that end, Google Sheets provides its users with specialized versions of VALUE for times and dates, they are TIMEVALUE and DATEVALUE respectively.
Convert Time Value to a Number in Google Sheets
A particular variant of the VALUE function used to convert time values to their decimal variant is the TIMEVALUE function.
TIMEVALUE(time_string)The function takes a time string as an argument and returns the decimal value as a fraction of an entire day or 24 hours.
This means that passing a time like 12:00:00 will return 0.5, 6:00:00 will return 0.25, and so on.
Let’s see what happens when we pass different time formats through the TIMEVALUE function:
As you can see from the image above, the result of the TIMEVALUE function resets every 24 hours.
So, no matter how much time has passed, the result will always be a fraction.
Learn More: Google Sheets: Convert Time to Number (3 Easy Ways)
Convert Date Value to a Number in Google Sheets
DATEVALUE is another special function that comes from VALUE.
DATEVALUE(date_string)The function converts a valid date to its underlying integer value assigned by Google Sheets.
But the function is not used to find the number of days that have passed since 12/31/1899 (whose value is 1 with DATEVALUE), but to determine whether the date string it is used on is a valid date or not.
This check is crucial since there are many ways that you can format dates in Google Sheets, and many of them are in text format by default.
So, if any date format passed through DATEVALUE gives an integer value, that date is valid and can be used for any date calculations.
The Different VALUE function Results Side by Side
3. Convert Text to Number in Google Sheets
Fundamentally, values in Google Sheets can be classified into two types: text and numbers.
When input in a Google Sheets worksheet, they align automatically in the cells to distinguish themselves. Left-aligned for text and right-aligned for numbers.
However, Google Sheets also allows its users to convert numbers to text quite easily through formatting.
On top of that, some values can appear as text if the wrong format is used. This is more common to see in the case of dates.
Even if it’s formatted, you can see whether a value is a text or not by using the ISTEXT function:
But whatever the case, we can use any of the functions mentioned above to situationally convert a text value to a number in Google Sheets.
For example, by using the Format menu:
As you can see, the “situation” that I talked about is that Google sheets cannot convert naturally occurring text or string values.
Only values that have underlying numerical data can be converted to a number in Google Sheets.
We will see similar results with the VALUE function:
=VALUE(B2)
Learn More: Google Sheets: Convert Text to Number (6 Easy Ways)
Final Words
That concludes all the ways we can use to convert a value to a number in Google Sheets. It is pretty straightforward considering the data-type conversion complications that might occur. But Google Sheets has it covered with its built-in restrictions and warnings (error messages).
Feel free to leave any queries or advice you might have in the comments section below.
