You often need to import data to Google Sheets from various file formats. The data could contain date, time, or currency values. When imported, these values may be stored as texts and make it difficult to use for calculations. However, you can convert them to numbers to make your work easier.
5 methods to convert Text to Numbers in Google Sheets
- Using DATEVALUE to convert a date value imported as text
- Using TIMEVALUE to convert time values imported as text
- Using MONTH to convert from month names to numbers
- Using REGEXREPLACE to convert currency imported as text
- Using Format Number to convert text to a number in the same cell
Method 1: Using DATEVALUE to convert date value imported as text
This method converts a date value imported as a text string to a number using the DATEVALUE function. When we enter a date into a cell, Google Sheets stores it as an integer. It starts from 1 which is 31st December 1900, 2 for 1st January 1900, 3 for 2nd January 1900 and so on.
Step 1: Identify the range of cells
Identify the range of cells in the date format that is to be converted to a number. Here, the data set has 6 rows of data in the date format to convert to a number.
Step 2: Enter the formula
Type the DATEVALUE formula which takes in one parameter; you’ll see the syntax as you type the formula.
Nest the DATEVALUE formula inside the ArrayFormula function to apply it to a range of cells.
If you want to convert just a single cell, for instance, the formula =DATEVALUE(A1) would convert cell A1 to a number.
If you’re trying to convert a range of cells, for instance, the formula =ArrayFormula(DATEVALUE(A1:A6)) will convert the range A1:A6 to a number.
Step 3: Press Enter to see the changes
To see the formula applied to your range of cells, press Enter.
Method 2: Using TIMEVALUE to convert time values imported as text
This method converts a time value to a number using the TIMEVALUE function. The time is stored as a fraction, which represents that fraction of a day that would have passed at any given time. For instance, noon is displayed as 0.5, since that’s when 50% of the day would have passed. Your output, of course, will always be between 0 and 1.
Step 1: Identify the range of cells
Identify the range of cells in the time format to be converted to a number. Here, I have 6 rows of data containing timestamps as text strings.
Step 2: Enter the formula
The use of TIMEVALUE here is pretty much the same as the DATEVALUE function. Type TIMEVALUE in the formula field and you’ll be able to see the syntax.
The formula =TIMEVALUE(A1) would convert A1 to a number. If you’re trying to convert a range though, use the Array Formula to apply the formula to a range of cells. In this case, the formula would be =ArrayFormula(TIMEVALUE(A1:A6)), which applies the formula to cells A1 to A6.
Step 3: Press Enter to see the changes
After entering the formula, press Enter. You’ll now see your selected range of cells converted to a decimal value that represents time.
Method 3: Using MONTH to convert a month names to numbers
This method converts a month imported in the form of a text string to a number using the MONTH function. The months are stored as an integer starting from 1 for January going all the way up to 12 for December.
Step 1: Identify the range of cells
Identify the range of cells containing the text strings to be converted to a number.
For instance, the following data set has all the months in column A.
Step 2: Enter the formula
Type in the formula MONTH which takes in the month as its parameter. However, you must add “&1” to avoid a value error since the parameter only allows numbers.
To convert a single cell, you could use the formula =MONTH(A1&1) to convert the month in cell A1 to a number.
Alternatively, you could use ArrayFormula to apply the function to a range of cells. =ArrayFormula(MONTH(A1:A12&1)) will convert the months from A1 to A12 to their corresponding number.
Step 3: Press Enter to see the changes
After entering the formula, press Enter. You’ll see the formula applied to your selected range of cells.
Method 4: Using REGEXREPLACE to convert currency imported as text
This method converts currency imported as text to a number using the REGEXREPLACE function.
Step 1: Identify the range of cells
Identify the currency text column or the range of cells that you’d like to convert to a number. For instance, the following data has 5 rows of currency text here.
Step 2: Enter the formula
The REGEXREPLACE function converts the currency text to a number. The Array Formula applies it to all the specified range of cells.
The formula is =ArrayFormula(REGEXREPLACE(A1:A5, “[^\d\.]+”,)*1)
Type the formula instead of copy-pasting to avoid any mistakes in the double-quotes.
The first parameter specifies the range of cells. The other parameter converts that range to a number.
Step 3: Press Enter to see the changes
After typing the formula, press Enter to see your currency text converted to a number.
Method 5: Using Format Number to convert to a number in place
All methods we discussed so far involve adding a formula to a different cell where you’ll display your output. If you want the output to replace your current data, consider using this method.
Note that you can’t use this method for converting months to a number, though.
Step 1: Select the range of cells
Select the currency text column or the range of cells that you would like to convert to a number. Highlight a cell and drag the cursor across the range of cells. For instance, using the same 5 rows of currency text here.
Step 2: Click Number from Format
Click Format from the main menu bar which opens up a drop-down. Select Number > Number.
Step 3: Ensure the changes are made
Clicking Number will change your currency text to a decimal number in the same cell.
Wrapping Up
This would’ve given you a good understanding of how different types of data are stored in Google Sheets. Now you know how to use that and convert different types of text data to a number easily.