How to Convert Text to Numbers in Google Sheets

In this article, we will learn how to convert text to numbers in google sheets.

We will learn different methods and approaches and will also learn which method to use in certain conditions. So first we will know what are numbers in google sheets? and what is text in google sheets? what is the difference between these two data types, and how do identify and verify data types in google sheets? So this article will be a complete package to learn how to convert text to numbers in google sheets with all the required prerequisites and factors involved while conversing data types.

Why we need to convert text to numbers in google sheets

Google sheets are more about data, it’s not about text, it’s about calculations not about formatting, it’s about values and formulas not about spelling and grammar. So, I believe you guys are getting confused over these terms, we will also learn how to distinguish among these terms in google sheets. We need to know that the data on your google sheet file is a number? or a text?

Before any calculation, if we apply a formula or a simple calculation on our data that we thought is a number but actually, it’s a text that is looking like a number so obviously, we will get an error, and these types of errors are mostly in a large dataset but we can not check every cell. We need to learn how to convert text to numbers in google sheets, so we will not face any problems while conversing, and also we will have full power over our data. So this is why we need to learn how to convert text to numbers in google sheets.

How to Convert Text to Numbers in Google Sheets

The steps involved to learn how to convert text to numbers in google sheets are mainly two.

Check Data Before Converting Text to Numbers in Google Sheets

First, we find out if the number is not a number, if so, we will convert it into a number to apply calculations and formulas.

So, you should learn this technique in two main steps.

How to check if its a number or text in google sheets?

We have some easy methods to check our data if it’s a text type or a number type.

So in this section, we will study the methods to check if data is a number type of text type.

First, let’s start talking about “Checking the alignment”

Checking alignment of data before converting Text to Numbers

In google sheets, all the alignment has some default setting, to get the benefit of this information you can check if the data is auto-aligned on the left side, then it’s a text type. Similarly, if the data is aligned on the right side by default, then it’s a number type. Now you may think it’s not an appropriate method. Yes, because you don’t know if the data is in its original formatting or if it has got some other formatting by other users. So, remember this method will only be useful when you have a small dataset that is created and maintained by you only.

To better understand this, I have an example that will show how you can check the data type by just knowing the default formatting rules.

Step 1

Notice the alignment of your data set

Step 2

Verify

Using ISTEXT() and ISNUMBER() formulas to Convert Text to Numbers in Google Sheets

We have the Boolean value returned by these functions, they tell true or false based on a binary situation. These are two simple self-explanatory formulas and can be applied to the cell(s).

To better understand we have an example for you to see the practice of both of the above formulas

Step 1

Write some random data (some text, some number)

Step 2

Check individual cells using =ISTEXT()

Step 3

Check individual cells using =ISNUMBER()

Step 4

Check a range of number

Step 5

Check a range of text

Step 6

Check a mixed range

For a mixed range above formulas =ISTEXT() and =ISNUMBER() get the result based on the first value

=ISNUMBER,

If the first value is a number, then =ISNUMBER() will return TRUE.

Similarly, if the first value is text, then =ISNUMBER() will return FALSE.

=ISTEXT

If the first value is text, then =ISTEXT() will return TRUE.

Similarly. if the first value is a number, then =ISTEXT() will return FALSE

Perform any Calculation to check to Convert Text to Numbers

This is one of the easiest and quickest methods to check whether a number is a number, or a text is a text. We will run the simplest calculation (addition, subtraction) and by seeing the result we identify is cell(s) have text data or number data.

For this, we have an example, that will show you how it is done.

Step 1

Identify the cells, for those you want to check the data type

Step 2

Run simple addition formula on it

Step 3

By seeing the answer, if it is summed with another number then obviously it’s a number else, it’s a text.

So, these are some methods, I think these are enough to use when you want to check if a cell contains a number or text.

Convert Text to Numbers after Data validation in Google Sheets

Now as we have learned how to find data types, we will move on to how to convert text to numbers in google sheets.

To convert we have many methods, and we will try to cover all of these.

Convert text to numbers using the main menu option

In this section, we will learn how to convert text to numbers in google sheets using the main menu option, for this, we have example data on which we will work and see how its implemented.

Step 1

Select all data

Step 2

In the main menu, go to Format > Number > Number

They will change to numbers with two decimal points

Step 3

You can increase or decrease decimal points in one click by clicking on decrease or decrease decimal places in the main menu

Step 4

Verify. Check your data is converted to the number

Convert Text to Numbers using the multiplication method

This section is a little complicated, in this method we will learn how to convert text to numbers in a google sheet using a simple multiplication rule, we will multiply the cell with 1, and by default, the multiplication nature will convert both operands to numbers before calculation thus we will get the same value as a number because will multiply with 1.

For multiplying any cell data with 1, I may have a few methods.

You can choose any method you think is easy to use

Direct Multiplication

Step 1

Identify the cell

Step 2

Select the cell, where you want to have the converted value

Step 3

Formula =cell*1 => =A1*1

Step 4

Verify the number using =ISNUMBER()

Convert Text to Numbers Using Multiply Function

Step 1

identify the cell

Step 2

Select the cell and write the function where you want to get the converted value

Step 3

Pass function arguments,

the first argument = cell reference

second argument = 1

separate the two arguments with a comma.

Step 4

Verify the number using =ISNUMBER()

Both of the above methods are good and recommended for a single cell.

Now we will see an array formula to convert a range of text into numbers using the multiplication method.

Convert Text to Numbers Using ArrayFormula

Step 1

identify the range

Step 2

Select a cell and write the =ArrayFromula in a cell where you want to get the converted value

Step 3

Pass first argument = range

Pass second argument = *

Pass third argument = 1

Step 4

Hit enter, and verify the number using =ISNUMBER()

So, these are the simplest methods to convert text to numbers in google sheets.

Convert text to numbers using the “Value” Function

The value function extracts the value of a cell, if 4 is written in a cell and formatted as a String, text, or date, then the value function has the power to convert it into Number. In this section, we will quickly learn how to use this function to convert text to numbers in google sheets.

Step 1

Select the data

Step 2

In other cell, write the function =Value()

Step 3

In the parentheses pass the cell

Step 4

Hit Enter, and you will get the converted values. Verify using =ISNUMBER()

Notes

FAQs

How to convert text to numbers in google sheets?

We discussed many methods that step by step teach you how to convert text to numbers in google sheets, read out the article and see which methods help you in your situation.

How to convert text to numbers in google sheets as an entire column?

You can use ArrayFromula to apply any formula to an entire column.

How to convert text to numbers in google sheets within the same column?

For changing the original data you can refer to the very first method taught in this article “Convert text to numbers using the main menu option”.

How to convert text to numbers in google sheets with the same decimal points?

Its default behavior of google sheets is to return two decimal points when you select the Number option as a Number formatting, you can increase or decrease decimal places using the main menu. It’s also taught in the first section “Convert text to numbers using the main menu option”.

Conclusion

Wrapping up the entire discussion, in the above article, we tried to cover the simplest and easiest way to first check the data whether they are text or numbers, then we moved on to learn the methods – how to convert text to numbers in google sheets, we learned three easy methods with step-by-procedure and screenshots to learn how to convert text to numbers in google sheets single cells, and how to convert text to numbers in google sheets entire range.

I hope you enjoyed this article and have learned many new things to implement in your day-to-day tasks. Please share the article with your friends and don’t forget to Subscribe to our blog. Thank you!!