When working with a spreadsheet, you often come across a problem where a formula isn’t functioning correctly. Most of the time, this is due to the data not being in the proper format in the cells. This can cause numerous issues in your work. First, your data won’t be consistently formatted; second, during calculations, some data may be ignored by the formulas because they aren’t recognized as numbers.
This article will address the question: “Can Google Sheets convert text to numbers?” and will also present a few easy methods to check if Sheets recognizes the data in cells as either text strings or numbers in the first place.
How to Determine if a Number is Text
Before we can use techniques to convert text to numbers, we first need to check if Google Sheets considers the data in the cell as a number or a text string. There are several ways to do this. Let’s take a look at some of them:
Check Alignment
This method is the quickest and easiest way to check the format of data in cells. By default in Google Sheets, text content is left-aligned, while numbers are right-aligned. Using this method, you can instantly tell if 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 data in cells. If you modify the cell alignment, you won’t be able to tell simply by looking at the cells. In this case, you’ll need to use a more advanced method to check if the data is text or a number.
Use the SUM Function
The SUM function is used to find the sum of numeric values in two or more cells, or in a range of cells. Here is the syntax of the function:
=SUM(val1, val2, ...)The val parameters are the number or range you want to add. We will use the SUM function to add values in order to check if the data in a cell is a number or a text value.
When this function is used to add text values in cells, the result is 0. However, when adding numbers, the function works as expected and returns the sum of the values in the cells.
Use the ISTEXT and ISNUMBER Functions
While the SUM function is useful, it doesn’t work when the range of cells contains both text and a number. In this case, the function will ignore the value in that cell and add up all the other values. This makes the SUM function unreliable when cells contain different types of values.
This is where the ISTEXT and ISNUMBER functions come in. They provide a foolproof method for checking the data type 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 value of the data. The formula will return a TRUE or FALSE value based on the function used and the data in the cell.
To use this function, we can enter the formula next to the cell and copy the formula to other cells using the fill handle. You can also use the ARRAYFORMULA to apply the function to multiple cells.
Recommended reading: Convert a Formula to Values in Google Sheets
How to Convert Text to Numbers in Google Sheets
Now that we know whether the data in the cell is a number or text, we can look at methods to convert text to numbers in Google Sheets.
Conversion with Multiplication
This is one of the simplest ways to convert text to numbers in Google Sheets. All you need to do is multiply the data in a cell by 1. This will force the display to be in number format.
There are several ways to do this. Here are some formulas you can use:
=cell * 1
=PRODUCT(cell, 1)
=ARRAYFORMULA(range * 1)
=ARRAYFORMULA(PRODUCT(range, 1))
In this example, we are using the first method. In the cell next to 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 cells of the spreadsheet or use the fill handle.
Conversion with the Format Menu
Using the Format menu in Google Sheets allows you to change the format of cells. Here are the steps to extract a number from a string using the Format menu:
- Select the cells you want to format.
- Click on Format in the top toolbar.
- In the dropdown 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 numbers. 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 toolbar, then click on Number.
- Click on Custom number format. You can enter custom instructions for formatting numbers there.
- Click Apply. This will save the format. Follow the above steps to apply the custom format.
Conversion with the VALUE Formula
You can use another simple function to convert text to numbers in Google Sheets. The VALUE formula. Here’s how it works:
=VALUE(text)The text parameter is the address of the cell containing the data you want 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 to change text to numbers in Google Sheets?
You can convert a text string to a number in your spreadsheet in several ways. One of the simplest ways is to use the Format menu by clicking on Format in the top toolbar, then clicking on Number. There, choose Number again, and the data in the selected cells will convert to number format.
How to change text to a number?
To change the format of data in a cell to a number, you can multiply the cell’s content by 1. You can also use the VALUE formula in Sheets to change data from a text string to a numeric value.
Conclusion of the Guide to Converting Text to Numbers in Google Sheets
All of these methods work within seconds. However, the quickest way for Google Sheets to convert text to numbers is to go through the Format menu, as it checks and makes the changes in one go.
Related content: Convert Phone Number Format in Google Sheets. You can also change many other things in the Format menu, such as capitalization. So, it’s a good idea to get familiar with this menu.
Google Sheets TEXT Function
