In this tutorial, you will learn how to convert text to numbers in Google Sheets.
How to Convert Text to Number in Google Sheets
Sometimes you may have text data that you want to use in a numerical calculation. For example, you may have a column containing currency data, but those numbers are stored in your sheet as plain text.
There are several different ways to handle the conversion. The simplest way is through using the number formatting options.
You may also use the VALUE function to convert text values into numbers.
Numbers can be formatted in a variety of ways as well. For example, dates written as text can be converted into date values as long as the date formats are valid and recognized by the program.
In this guide, we will show you several methods to quickly convert text to number in Google Sheets.
We will explain how to use a formula to achieve this, as well as an automated feature available in the toolbar.
How to Convert a Text String to a Numerical Value in Google Sheets
Here’s how we can convert a text string in Google Sheets to its corresponding numerical value.
Step 1
First, we’ll show you how to convert text to numbers using the formatting tool.
Select the cell or range of cell containing the text you want to convert.
Step 2
Click on the More formats shortcut in the toolbar to reveal the list of possible number formats.
Step 3
Next, we’ll explain how to convert text to numbers using Google Sheets functions.First, select an empty cell where you want to output the numerical result.
In our example above, we want to convert the text values in column A into the equivalent numerical data in column B.
Step 4
We can use the VALUE function to convert a cell’s contents into a number.
We can use the AutoFill feature to fill out the rest of the column as seen below.
Step 5
The VALUE function also works on strings representing valid dates.
Step 6
Sometimes, we want to extract a number from a given string. For example, if we are given the string “250 dollars”, we want to find a way to retrieve the number 250.
In Google Sheets, we can use a combination of SPLIT and CONCATENATE functions to extract the desired number.
The formula takes a text string that contains both numeric and non-numeric characters, splits it into separate groups of non-numeric characters, and uses these non-numeric characters to reveal the numeric sections of the original string.
Step 7
We can determine if our output is truly numerical by using the ISNUMBER function.
If the output of the ISNUMBER function is TRUE, then the input cell contains numerical data.
Summary
This guide should be everything you need to convert text to numbers in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.