When working on a spreadsheet, you often encounter an issue where a formula isn’t working properly. Most of the time, this is due to the data not being in a proper format in the cells. This can cause a lot of issues in your work. First, your data won’t be consistently formatted; secondly, when performing calculations, some data may be ignored by the formulas as it won’t be detected as a number.
This article will answer the question, “Can Google Sheets Convert Text to Number?” as well as some easy methods to check whether Sheets recognizes the data in cells as text strings or numbers in the first place.
Table of Contents
How to Tell When Number Is Text
Before we can employ the techniques to convert text to a number, we first need to check whether Google Sheets considers the data in the cell a number or a text string. There are multiple ways to do this. Let’s take a look at some of them:
Check the Alignment
This method is the fastest and easiest way to check the data’s format inside the cells. By default in Google Sheets, text content is aligned to the left, while numbers are aligned towards the right. Using this method, you can tell by just a glance whether the data in a cell is a number or a text value.
However, note that this method only works if you’re using the default alignment of the data in the cells. If you change the alignment of the cells, then you won’t be able to tell just by looking at the cells. If this is the case, you will have to use a more advanced method to check if the data is text or a number.
Use the SUM Function
The SUM function can find the total of numerical values in two or more cells or a cell range. Here is the syntax for the function:
=SUM(val1, val2, ...)
The val parameters are the number or the range you wish to add together. We will use the SUM function to add the values to check whether the data in a cell is a number or a text value.
When this function is used to add text values in cells, the output is 0. However, when adding numbers, the function works as expected and returns the sum of the values in the cells.
Use ISTEXT and ISNUMBER
Although the SUM function is useful, it doesn’t work when a cell range contains text and a number. When this happens, the function will ignore the value in that cell and sum up all the other values. This makes the SUM function unreliable when cells have different types of values.
This is where the ISTEXT and ISNUMBER functions come in. They provide a fool-proof method for checking the type of data in a cell. Here is the syntax for the ISTEXT and ISNUMBER functions:
= ISTEXT(val) = ISNUMBER(val)
The val parameter is the cell containing the data value. The formula will return a TRUE or FALSE value depending on the function used and the data inside the cell.
To use this function, we can enter the formula beside the cell and copy the formula to the other cells using the fill handle. Alternatively, you can use the ARRAYFORMULA to use the function in multiple cells.
Related Reading: Convert Formula to Values in Google Sheets
How to Convert Text to Number in Google Sheets
Now that we know if the data in the cell is a number or text, we can look at methods to convert the text to number Google Sheets.
Google Sheets Convert to Number by Multiplication
This is one of the easiest ways to convert text to number in Google Sheets. All you have to do is multiply the data in a cell by 1. This will force the output to be shown in a number format.
There are multiple ways you can do this. Here are a few formulas you can use:
=cell * 1 =MULTIPLY(cell, 1) =ARRAYFORMULA(range * 1) =ARRAYFORMULA(MULTIPLY(range, 1))
In this example, we are using the first method. In the cell beside the one containing the values, enter the cell name, an asterisk sign, and 1. Press Enter to execute the formula. You can use the suggested autofill feature to fill the formula in all spreadsheet cells or by using the fill handle.
Google Sheets String to Number Using the Format Menu
Using the format menu in Google Sheets allows you to change the format of the cells. Here are the steps you need to follow to extract a number from a string with the format menu:
- Select the cells you wish to format.
- Click on Format in the top bar.
- In the drop-down menu, click on Number.
- Click on Number in the sub-menu.
Once the process is completed, the values in the selected cells will be formatted as a number. They should also automatically align to the right.
In the same menu, you can also create a custom number format. While selecting the cells:
- Click on Format in the top bar and then Number.
- Click on Custom number format. Here you can type in custom instructions for the formatting of the numbers.
- Click Apply. That will save the format. Follow the steps mentioned above to apply the custom formatting.
Google Sheets Text to Number With the Value Formula
You can use another simple function to convert text to number in Google Sheets. The VALUE formula. Here is how it works:
=VALUE(text)
The text parameter is the cell address containing the data you wish to convert to a number.
To use this formula, enter it in the desired cell and write the cell address as the parameter. Press Enter to execute the formula.
Frequently Asked Questions
How Do I Change Text to Number in Google Sheets?
You can convert a text string into a number in your spreadsheet in several ways. One of the simplest ways is to use the format menu, accessed by clicking on Format in the top bar and then clicking on Number. There, choose Number again, and the data in the selected cells will change to number formatting.
How Do I Change a Text to a Number?
To change the data format in a cell to number, you can multiply the cell’s contents by 1. You can also use the VALUE formula in Sheets to change the data in a cell from a text string to a number value.
Wrapping Up the Google Sheets Convert Text to Number Guide
Any of these methods work in under a few seconds. But, the quickest way for Google Sheets to convert text to number is to go through the format menu as it checks and changes in a single move.
Related Content: Convert to Phone Number Format in Google Sheets. You can change plenty of other things in the format menu too, like capitalizations. So, it’s a great idea to familiarize yourself with the format menu.
