Google Sheets numbers as text

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. 

This Article Covers:

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 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: 

=TO_PURE_NUMBER(A2)

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. 

=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 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!

Sumit

Spreadsheet Expert at Productivity Spot |+ posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

How do I make a cell plain text in Google Sheets?

How do I concatenate a number to a string in Google Sheets?