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.
- 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.
Notice the alignment of your data set
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
Write some random data (some text, some number)
Check individual cells using =ISTEXT()
Check individual cells using =ISNUMBER()
Check a range of number
Check a range of text
Check a mixed range
For a mixed range above formulas =ISTEXT() and =ISNUMBER() get the result based on the first value
If the first value is a number, then =ISNUMBER() will return TRUE.
Similarly, if the first value is text, then =ISNUMBER() will return FALSE.
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.
Identify the cells, for those you want to check the data type
Run simple addition formula on it
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
- 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.
Select all data
In the main menu, go to Format > Number > Number
They will change to numbers with two decimal points
You can increase or decrease decimal points in one click by clicking on decrease or decrease decimal places in the main menu
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
Identify the cell
Select the cell, where you want to have the converted value
Formula =cell*1 => =A1*1
Verify the number using =ISNUMBER()
Convert Text to Numbers Using Multiply Function
identify the cell
Select the cell and write the function where you want to get the converted value
Pass function arguments,
the first argument = cell reference
second argument = 1
separate the two arguments with a comma.
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
identify the range
Select a cell and write the =ArrayFromula in a cell where you want to get the converted value
Pass first argument = range
Pass second argument = *
Pass third argument = 1
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.
Select the data
In other cell, write the function =Value()
In the parentheses pass the cell
Hit Enter, and you will get the converted values. Verify using =ISNUMBER()
- 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.
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”.
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!!