Google Sheets: Convert String to Number (4 Easy Ways)

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.

left aligned string can be converted to right aligned number in google sheets

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:

strings don't give a result when used in number functions

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)

using isnumber function can determine whether the value is a number or not

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.

sample dataset with numbers as a 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

navigating to the number option from the format tab in google sheets

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.

convert string to number in google sheets using the format menu

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

navigating to the custom number format option from the format tab in google sheets

Applying the “#.0” formatting to convert the string to the equivalent number in Google Sheets:

applying a custom number format 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

multiplying string number by 1 to convert it to number in google sheets

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)

different value unction variants and their conversion results

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:

different value unction variants and their conversion results

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.

navigating to the apps script option from the extension tab in google sheets

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:

how the script looks like in the apps script window

Step 3: Click Run to apply the script. Google Sheets may prompt you for permission. Allow it.

press run to apply the script to convert string to number in google sheets

Step 4: Go back to the Google Sheets spreadsheet tab and Refresh the window. The Number menu should now appear:

the number menu that is created by apps script

Step 5: Select the range of cells that contain the string and apply the script from the menu.

applying script to convert string to number in google sheets

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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo