Mehrab Imtiaz
Google Sheets has multiple ways to convert a string to a number. It can be as easy as using built-in functions or features of the applications or going deeper into the realms of using Apps Script.
No matter which approaches you take, it is always a good idea to first determine the form the data is in. Is it a number or a text?
Check Whether a Value is a String or a Number First
It is better to first determine whether your dataset contains string values before stepping into conversion.
There are multiple ways to go about it and all of them are quite simple:
1. Check Value Alignment in a Cell
This first method is simply about “eyeballing” it.
In Google Sheets, values are aligned in a cell according to their base formats:
- Text or string values are left-aligned.
- Numerical values are right-aligned.
So, if a number value shows itself as left-aligned in a cell, it is most likely in the text format and perhaps needs to be converted.
2. Check with a Number Function like SUM
Using simple number functions over a range of suspicious cells is another way to determine their base formatting.
The function that I am going to use is SUM. Let’s see what happens when we apply this function to the two columns:
As you can see, the column with the text “number” values gives us a sum of 0 as a result, whereas the column with actual numbers gives us a summed value.
Text or string values cannot be used with number functions in Google Sheets.
3. Check with ISNUMBER Function
“Eyeballing it” is not always reliable as cell values can be formatted in any way in Google Sheets.
Also, some numerical string values can be seen as numbers by Google Sheets.
So, the application itself provides us with a function to confidently determine whether a value is a string or a number. It is the ISNUMBER function.
ISNUMBER(value)
This being a function, ISNUMBER also has the advantage of being placed anywhere in the worksheet to determine the value type.
Alternative to this is the ISTEXT function which does the opposite: find whether the value is a text or not.
4 Ways to Convert String to Number in Google Sheets
1. Use Google Sheets’ Format Menu to Convert String to Number
What better way to format or convert a value in Google Sheets than using the Format menu?
Step 1: Select the range of cells that contains the string.
I have kept the ISNUMBER formula active in the adjacent column. This will show the transformation from the string to the number.
Step 2: Navigate to the Format tab of Google Sheets. Here you’ll find the Number option in the Number submenu.
Format > Number > Number
Alternatively, you can also find the Number option from the More formats menu of the toolbar (the 123 icon).
Step 3: While we have different forms of numbers in the column (integers, currencies, and even a date), selecting the Number option will convert the string to a single uniform number format.
If you are looking for more customized number formatting to convert to then you can opt for the Custom Number Format feature.
This will allow you to change your number to any format, be it integers, decimals, include special symbols and so much more.
Format > Number > Custom number format
Applying the “#.0” formatting to convert the string to the equivalent number in Google Sheets:
2. Use Multiplication on a Number String to Convert It
A unique way to convert a string to a number in Google Sheets is by using Multiplication.
It sounds weird, but it is possible albeit with a big but:
The string or text has to be numerical values.
Or values that can be recognized as a number that just happens to be a string type data. Like the ones we’ve used in the previous examples.
This conversion simply involves multiplying the string “number” with 1.
=B2*1
3. Use the VALUE Function to Convert String to Number in Google Sheets
If you want to present your results in a separate cell leaving the original data untouched, it is best to use a function.
A particular function of Google Sheets that can convert a string to a number by extracting the underlying value (the pure number) is the VALUE function.
VALUE(text)All you have to do is simply pass the string (as a cell reference) through the VALUE function:
=VALUE(B2)
Specialized VALUE Functions
The VALUE function itself is quite generalized considering the different types of numbers we may have, like currencies, dates, and times.
To that end, Google Sheets provides its users with specialized versions of the VALUE function to cater to these different numerical values.
Two of which are the DATEVALUE and TIMEVALUE functions.
As their names suggest, these functions can extract the date and time values from a string.
Let’s see the resulting conversions of each of these functions side by side:
4. Use Apps Script to Convert String to Number
The Google Sheets Apps Script is a wonderful feature for users looking to make the next level of customization in the application.
It is a niche approach that takes advantage of writing a customized script to make user-tailored changes.
In the case of today’s topic of converting a string to a number, it is not really necessary, but it is good to know that it is possible.
Let’s see how it’s done:
Step 1: Navigate to the Extensions tab to find the Apps Script option.
This will take you to a separate Apps Script window in your browser. It is recommended that you name your project.
Step 2: Enter the following scripts:
function convertString() {
SpreadsheetApp.getActive().getActiveRange().setNumberFormat("####.00");
};
Converts the active cells to the number format. The format that the cell value will be changed to is “####.00”.
You can change to any other format that you desire from here.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Number')
.addItem('Convert to Number', 'convertString')
.addToUi();
};
Creates a menu for the script. In this case, it is called the Number menu where you can find the Convert to Number option. Clicking on this option will run the script over the selected cells.
The whole thing looks like this in the Apps Script window:
Step 3: Click Run to apply the script. Google Sheets may prompt you for permission. Allow it.
Step 4: Go back to the Google Sheets spreadsheet tab and Refresh the window. The Number menu should now appear:
Step 5: Select the range of cells that contain the string and apply the script from the menu.
As you can see from the image above, all values with underlying numerical data have been successfully converted.
Final Words
That concludes all the ways we can use to convert a string into a number in Google Sheets.
While the idea and methodology are simple, you must know that any kind of data type conversion can bring about unexpected complications.
Feel free to leave any queries or advice you might have for us in the comments section below.
