You may think in what scenario will I need to convert text to numbers in a Google document or in Google sheets.
Think about it this way: if you are given a spreadsheet with numbers, it will be easy to pull off some of the formulas required to get a proper calculation of 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.
When you are dealing with measurements like currency in which a number is needed for the analysis, formulas are extremely useful and can save a lot of time.
In this article, we’ll go over how you can convert the “text” format into “number” format in Gooogle Sheets to more easily satisfy the formula constraints.
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:
Replace Dots with 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 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.
Converting Data with Currency Text to Number
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:
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 will use both “Split” and “Concatenate” functions at the same time.
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.
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 regards to extracting the number from the text.
So these are a few methods you can use to convert text to numbers in Google Sheets.
I hope you found this tutorial useful!
Other Google Sheets tutorials you may like: