If you are given a spreadsheet with numbers, it will be easy to pull off some of the formulas required to properly calculate whatever it is that you are trying to analyze. On the other hand, the “text” format of those numbers can make it very difficult and sometimes even impossible to pull these same formulas off. This is why you may need to convert text to numbers in Google Sheets.
When dealing with measurements like currency in which a number is needed for the analysis, formulas are extremely useful and can save time.
In this article, we’ll go over how you can convert the “text” format into “number” format in Google Sheets to more easily satisfy the formula constraints. But, the simplest way to do this that this Google Sheets convert text to number guide will cover is to simply hight light the data and head to Format > Number, and click Number.
This Article Covers:
How to Convert Text to Number in Google Sheets
Here’s how to convert text to number format in Google Sheets:
Step 1: Select the data
Step 2: Go to Format > Number
Step 3: Select Number.
If you’re still having trouble, consider taking a full Google Sheets course.
Value is a Text and Cannot be Coerced to a Number.
Sometimes, while trying to work with values on Google Sheets using, you may get an error “X” is a text and cannot be coerced to a number. This usually happens if you’re text does not have any number values, but it can also happen with number values when you import data into Google Sheets. This also occasionally happens when you have dates as text in the data you’re importing.
The way to fix this error is by converting the text to numbers using one of the methods below.
How to Tell a Number is Text Using the SUM Function
Sometimes your values in Google Sheets may appear to be in the number format, but they are actually text. In this case, there are ways you can check to see if a number is text.
Here’s how to tell a number is text using the SUM function:
Step 1: Type the =SUM formula in a cell
Step 2: Select the cell or range of cells with the text
Step 3: Press Enter.
If the formula returns results, then your values are numbers, but if they return a 0, then it means that although they look like numbers, they are actually text.
However, the SUM function doesn’t work when there is data that has numbers and text combined in one cell. Also, if you have a mixture of text and number values and text values in your data, the SUM function will only add up the number values, so you’ll notice that the results seem to fall short. However, this might be difficult to tell if you’re dealing with large values and large volumes of data.
How to Tell a Number is Text Use ISTEXT and ISNUMBER
You can also tell if a number is a text using the ISTEXT and ISNUMBER functions. ISTEXT is used to verify that a value is a text, while ISNUMBER is used to verify that a value is a number.
This is more useful when you have a combination of numbers and text together in one cell.
These formulas will usually return either TRUE or FALSE depending on whether the value is a number or text.
Let’s look at our example above. In this case, we have used the formula
=ISTEXT(A2)
and
=ISNUMBER (A2)
The ISTEXT formula returns true if the value is a text and false if the value is a number. Meanwhile, the ISNUMBER formula returns true if the value is a number and false if the value is text or any other format.
Converting Text To Numbers Using VALUE Function
There are a few formulas you can use to convert text to numbers in Google Sheets
When looking to convert text into a number, the first option you have is to use the “Value” function as such:
=VALUE(A2)
Replace Dots with a Comma to Convert Text to Numbers
Unfortunately, this will not always work when you receive data from a file in which you need to convert the “text” format into a “numbers” format.
Perhaps you are given files in which the commas have been replaced by “.” or “periods” and are being read in the “text” format.
This is sometimes the case when you get from countries that use a dot instead of a comma.
In this case, you will want to use another formula:
=REGEXREPLACE(A2, "[.]", ",")
This works in a very similar way to the “REGEXTRACT()” function in that it finds and replaces the “text” format of the number into the “number” format of the number.
This works by replacing the “.” with a “,”. This will help convert the number “143.244” to “143,244” which is the same as “143244” that can be used in a formula for a calculation.
You can actually test out whether or not this works by using the number generated from the “text” format of that number by using it in a calculation.
Google Sheets Convert Text to Number: Currency
When you import data from another document into Google Sheets, you may find yourself with a “$” symbol or another currency format in front of the number as a “text”. You can do this by using the “To Pure Number” formula as such:
=TO_PURE_NUMBER(A2)
Convert Text to Number in the Format Menu
The format menu contains many options for formatting your data on Google Sheets. One of them is the number formatting option. This lets you convert any form of data to numbers, including text. It will usually also add decimal points to the number after converting it.
Here’s how to convert text to numbers in Google Sheets:
Step 1: Select the text data.
Step 2: Go to Format > Number
Step 3: Select Number.
Once you’re done, your text will be converted to numbers. You’ll notice that the alignment may also change. This is because numbers in Google Sheets are in the right alignment by default. You can also test this out using the SUM function.
Extracting Numbers From Text
Sometimes, you might import a piece of text from another data set. This text could be an entire sentence that has numerical values written inside of it.
For example, cell A1 contains the following text:
Last month I paid $200 for blog maintenance. This month I am expected to only pay $150 for fewer services.
Let me show you two formulas to get this done:
Formula Method 1
In order to extract the numbers from this text, we can split them by using “Split” and “Concatenate” functions at the same time.
=SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789")))
As you can see, the “$200” part did not get rid of the sign. If this is important, we can easily go back and use the “Pure Number” formula mentioned earlier.
Related: Google Sheets CONCATENATE formula
Formula Method 2
We can also use the “Split” function in conjunction with the “REGEXREPLACE” function.
=SPLIT(REGEXREPLACE(A1, "[^d.]+", "|"), "|")
As you can see, both methods do the same thing in regard to extracting the number from the text.
Convert Text to Numbers with App Scripts
Another way to convert text to numbers is by using the Apps script. App scripts is a very useful tool in Google Sheets for automating functions.
You convert text to Numbers in Google Sheets using App scripts, but you can also go as far as to create a button to automate this process. Here’s how:
Step 1: Go to Extensions > App Scripts.
Step 2: Copy and paste the script below in the script window.
function numberFormat() {
SpreadsheetApp.getActive().getActiveRange().setNumberFormat("####.00");
};
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Number')
.addItem('Change to Number Format', 'numberFormat')
.addToUi();
};
Step 3: Save the script and click Run.
App scripts may ask you to review your permissions, in which case you need to give access to your Google account.
Click Review permissions and choose your Google account. If you’re not using the chrome browser, you may get a prompt saying, “Google hasn’t verified this app”. In this case. you have to go a step further and go to Advanced and go to Untitled project.
Frequently Asked Questions
How Do You Replace Words With Numbers in Google Sheets?
There are several different ways you can use to replace words with numbers in Google Sheets. The simplest way is by using the REPLACE function. Simply type =REPLACE into the cell with the text you want to replace, and then input the number you want to replace the text with, and the number of times you want it to occur.
This could be helpful when you want to use Google Sheets for stock prices. It also comes in handy when you paste text from another source.
How Do I Convert Text to Numbers in Google Spreadsheet?
There are several different ways you can use to convert words to numbers in Google Sheets. The simplest way is to navigate to the Format menu, then click Number, and Number again.
Conclusion
So these are a few methods you can use to convert text to numbers in Google Sheets. We’ve also shown you how to check whether a value is a text or a number in Google Sheets.
I hope you found this Google Sheets convert text to number tutorial useful!
Related:
- Convert Time to Decimal in Google Sheets
- How to Transpose Data in Google Sheets
- Change Text to Dates in Google Sheets
