How to Convert Text to Numbers in Google Sheets

how to convert text to numbers in google sheets 1

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.

See also  How to use Google Translate Function in Google Sheets (Complete Guide)

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.

  • Checking alignment of data
  • Using ISTEXT() and ISNUMBER() formulas
  • Perform any calculation to check

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

how to convert text to numbers in google sheets 1

Step 2

Verify

how to convert text to numbers in google sheets 2

See also  How to Find Column Letters in Google Sheets [Easy Guide]

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)

how to convert text to numbers in google sheets 3

Step 2

Check individual cells using =ISTEXT()

how to convert text to numbers in google sheets 4

how to convert text to numbers in google sheets 5

Step 3

Check individual cells using =ISNUMBER()

how to convert text to numbers in google sheets 6

how to convert text to numbers in google sheets 7

Step 4

Check a range of number

how to convert text to numbers in google sheets 8

how to convert text to numbers in google sheets 9

Step 5

Check a range of text

how to convert text to numbers in google sheets 10

how to convert text to numbers in google sheets 11

Step 6

Check a mixed range

how to convert text to numbers in google sheets 12

how to convert text to numbers in google sheets 13

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.

how to convert text to numbers in google sheets 14

=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

how to convert text to numbers in google sheets 15

See also  How to Automatically Sort Dataset in Google Sheets

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

how to convert text to numbers in google sheets 16

Step 2

Run simple addition formula on it

how to convert text to numbers in google sheets 17

Step 3

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

how to convert text to numbers in google sheets 18

how to convert text to numbers in google sheets 19

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.

See also  How to use FREQUENCY Function in Google Sheets [Complete Guide]

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
  • Convert text to numbers using the multiplication method
  • Convert text to numbers using the “Value” function

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

how to convert text to numbers in google sheets 20

Step 2

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

how to convert text to numbers in google sheets 21

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

how to convert text to numbers in google sheets 22

how to convert text to numbers in google sheets 23

Step 4

Verify. Check your data is converted to the number

how to convert text to numbers in google sheets 24

See also  How to Make Organizational Chart Template in Google Sheets

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

how to convert text to numbers in google sheets 25

Step 2

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

how to convert text to numbers in google sheets 26

Step 3

Formula =cell*1 => =A1*1

how to convert text to numbers in google sheets 27

how to convert text to numbers in google sheets 28

Step 4

Verify the number using =ISNUMBER()

how to convert text to numbers in google sheets 30

how to convert text to numbers in google sheets 31

See also  How to Show Formulas in Google Sheets [Complete Guide]

Convert Text to Numbers Using Multiply Function

Step 1

identify the cell

how to convert text to numbers in google sheets 32

Step 2

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

how to convert text to numbers in google sheets 33

Step 3

Pass function arguments,

the first argument = cell reference

second argument = 1

separate the two arguments with a comma.

how to convert text to numbers in google sheets 34

how to convert text to numbers in google sheets 35

how to convert text to numbers in google sheets 36

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

how to convert text to numbers in google sheets 38

Step 2

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

how to convert text to numbers in google sheets 39

Step 3

Pass first argument = range

how to convert text to numbers in google sheets 40

Pass second argument = *

how to convert text to numbers in google sheets 41

Pass third argument = 1

how to convert text to numbers in google sheets 42

Step 4

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

how to convert text to numbers in google sheets 43

how to convert text to numbers in google sheets 44

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

how to convert text to numbers in google sheets 45

Step 2

In other cell, write the function =Value()

how to convert text to numbers in google sheets 46

Step 3

In the parentheses pass the cell

how to convert text to numbers in google sheets 47

how to convert text to numbers in google sheets 48

Step 4

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

how to convert text to numbers in google sheets 49

See also  How to Import CSV files to Google Sheets (Beginner's Guide)

Notes

  • Always use any of the above techniques that best fit your problem
  • Do not go for easy methods, if it does not solve your problem properly
  • Remember that, when using a function we use a comma as a separator, and when using direct formula then we use the operation sign as a separator
  • Always remember that you can manually set the formatting of text exactly like numbers, so don’t for the alignment method if your work is somehow important.
  • Always use ArrayFormula when you are working on ranges instead of a single cell.

See also  How to Add a Trendline in Google Sheets [Complete Guide]

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”.

See also  How to Freeze a Row and Column in Google Sheets

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

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general