Convert Text to Numbers in Google Sheets- A Step-by-Step Guide

When working with Google Sheets, you may sometimes encounter situations where values are stored as texts, instead of numbers. These could be anything from dates to phone numbers and even numerical values. Unfortunately, treating them as text instead of numbers can trigger errors in calculations, making it nearly impossible to use functions like AVERAGE and SUM.

Thankfully, Google Sheets has provided a simple and straightforward solution to this problem. In today’s Google Sheets tutorial, we will show you a simple step-by-step process you can leverage to convert text to numbers in Google Sheets.

If you have always wanted to learn how to convert text to numbers in Google Sheets seamlessly, you’re welcome to read today’s guide, as it has everything you’re looking for and more. Let’s get started right away.

How to know when a number is a text

Before we show you how you can convert text to numbers in Google Sheets, it’s important we take you through the basics. In this section, we will show you how to know when data in a cell is a number or a text string. And since there are different ways to do this, we will show you a couple of them:

Look at the alignment

This method is among the quickest ways to check the data format inside the cells. If you have been using Google Sheets for a while, you’ll notice that Google Sheets typically aligns text content to the left. On the flip side, numbers are aligned to the right.

By leveraging this method, you can easily tell whether the data in a cell is a text value or a number.

If you look closely at the image below, you’ll notice that the text values are aligned to the left while numbers are aligned to the right.

Note: The method we just showed you only works when using Google Sheets default alignment. This means if you tweak the alignment for the cells, you won’t be able to tell whether the data in a cell is a text or number by merely looking at the cells. And should that be the case, you’ll have to use other advanced methods to tell if the data in your spreadsheet is a text or a number.

Using the SUM function to tell when a number is a text

As we mentioned earlier, looking at the alignment alone isn’t a particularly great way to tell when a number is a text, especially in a situation where you aren’t using Google Sheets’ default alignment. That’s why we want to show you how to use the SUM function to tell whether a number is a text or not.

The SUM function is a particularly brilliant formula for finding the total of numerical values in a cell range. Here, take a look at the syntax for the SUM function:

=SUM(val1, val2, …)

The val parameters in the syntax above refer to the number or range we would like to add together. We will use the SUM function to add the values in Columns A and B to check whether the data in those cells represent a number or a text value.

Here is how to do that:

Start by clicking the cell where you’d like the result to be generated. For this example, we will use cell A7.

Now, head to the formula bar and type in the following formula:

=SUM(A2:A6)

Finally, hit enter.

You should notice that the result generated was 0. What this simply means is that the values in cells A2:A7 are text values.

Having gotten the result for text values, do the same for column B:

Start by selecting the cell where you want the result generated. For this example, we will select Cell B7.

With that done, head to the formula bar and type in the following formula:

=SUM(B2:B6)

Hit enter on your keyboard.

If you did exactly as we showed you, Google Sheets will automatically add all the values in the cells and generate the result in cell B7.

Understanding the interpretation

If you look at the image below closely, you’ll notice that when we use the SUM function to add the text values in our spreadsheet, the output is 0. On the other hand, when we use the same formula to add the numbers in column B, the function worked exactly as designed and returns the sum of the values in the cells.

Using ISTEXT and ISNUMBER to tell when number is text

Even though the SUM function is a useful tool for telling when a number is a text, it has a few lapses you should be aware of. For instance, the SUM function doesn’t work when a cell range contains a text and a number.

What this simply means is that when you apply the SUM function to a cell range that houses both text and numbers, the function ignores the text value in that cell and sums up all the other values it considers as numbers.

So when dealing with cells that have different types of values, using the SUM function will be unreliable.

Thankfully, you can use a more advanced method, which is the ISTEXT and ISNUMBER functions. These functions provide a comprehensive solution for checking the type of data in a cell. Please find below the syntax for the ISTEXT and ISNUMBER functions.

= ISTEXT(val)

= ISNUMBER(val)

The val parameter in the syntax above represents the cell containing the data value. When you apply these formulas to your spreadsheet, it will typically return a TRUE or FALSE value, depending on the function used as well as the data in the cell.

Read on as we show you how to use the ISTEXT and ISNUMBER functions to tell whether a value is a text or a number. We will apply these functions to our earlier example. But this time, we will only focus on the values in Column B.

Start by entering a header for ISTEXT and ISNUMBER functions. For this example, we will use cell C1 for ISTEXT and cell D1 for ISNUMBER.

With your headers entered. Select cell C2, then head to the formula bar and type in the following formula:

=ISTEXT(B2)

Hit Enter on your keyboard.

If you did exactly as we showed you above, you should have a spreadsheet that looks like this:

From the image above, you’ll notice that we only used the ISTEXT function for cell C2. To get the values for other cells, we can use Google Sheets’ auto-fill option. The video below shows you how to go about that:

Now that we have shown you how to use the ISTEXT function to know whether a value is a text or not, let’s show you how to use the ISNUMBER function.

Select the cell where you’d like the result generated. For this example, we will select cell D2.

Now, navigate to the formula bar and type in the following formula:

=ISNUMBER(B2)

Finally, hit enter and watch the magic happen.

After applying the ISNUMBER function as detailed above, here is what our worksheet looks like:

As we did for the ISTEXT function, we will also generate the result for other cells using the auto-fill option.

From the examples we covered for this method, it’s easy to see how you can use the ISTEXT and ISNUMBER functions to determine whether a number is a text or not. Now, we want to show you the different ways to convert text to numbers in Google Sheets. Let’s get started, shall we?

Converting text to numbers in Google Sheets

Converting text to numbers in Google Sheets is a pretty straightforward process. And in this guide, we will highlight different ways you can seamlessly convert text to numbers in Google Sheets. Read on as we show you some of the easy ways to convert text to numbers in Google Sheets

Convert text to numbers in Google Sheets using multiplication

If you’re looking for an easy way to convert text to numbers in Google Sheets, using the multiplication method wouldn’t be a bad idea. When using this method, all you’re basically doing is multiplying the data in a cell by 1. What this essentially does is force the output to be shown in a number format.

The cool thing about using the multiplication method is that there are several ways to do it. Here are some formulas you can use to convert text to numbers in Google Sheets using the multiplication method:

=Multiply(cell,1)
=ARRAYFORMULA(MULTIPLY(range,1))
=cell * 1
=ARRAYFORMULA(MULTIPLY(range,1))

To demonstrate how to convert text to numbers in Google Sheets, we will use the following sample data below.

For this example, we will be using the third formula (=cell * 1) to convert text to numbers in Google Sheets. Here is how to go about it:

Start by selecting the cell where you want the result generated. For our guide, we will use cell B2.

With the cell selected, navigate to the formula bar and type in the following formula

=cell * 1

After typing in the formula, hit the Enter button and wait for a couple of seconds for the result to be generated.

Since we only generated results for cell B2, we need to do the same for other cells. But instead of going at it manually, we can use Google Sheets’ auto-fill option to generate the result for other cells.

The video below shows you exactly how to go about it:

Convert text to numbers in Google Sheets using the Format Menu

Another exciting way to convert text to numbers in Google Sheets is using the Format menu. Like the method we earlier discussed, this too is super easy and straightforward. But don’t take our word for it, check it out for yourself.

For this method, we will use the following sample data.

Start by selecting the cells you’d like to format. For this guide, we will highlight cells A2:A6.

With the cells you’d like to format selected. Head over to the Format menu, from the drop-down menu, click on Number, and select Number in the sub-menu.

After completing the process we highlighted above, you should notice that the selected cells have been formatted as numbers. Also, you should notice that the numbers have been automatically aligned to the right.

Here, take a look at what our spreadsheet looks like:

Convert text to numbers in Google Sheets using the Value formula

So far we have covered how to convert text to numbers via multiplication and the Format menu, now we want to show you how to convert text to numbers in Google Sheets using the Value formula.

Here is the formula we will use to achieve that:

=Value(text)

The text parameter in the formula above refers to the cell housing the data you’d like to convert to numbers.

Here is how to use the Value formula to convert text to numbers in Google Sheets. For this guide, we will use the same sample data we used for the other methods we earlier discussed.

Start by selecting the cell where you want the result to be generated. For this guide, we will use cell B2.

After choosing the cell where you want the result generated. Head to the formula bar and type in the following formula:

=Value(A2)

Finally, hit Enter. The result should be generated almost instantly.

From the image above, you’ll notice we only generated for cell B2, we need to do the same for the other cells. A simple way to do this is to use Google Sheets’ auto-fill option. This video shows you how to go about that.

Final thoughts

Converting text to numbers in Google Sheets is super easy. And you don’t need to have a ton of experience with Google Sheets to do it.

In today’s guide, we showed you how to know when a number is a text. After that, we showed you three different ways to convert text to numbers in Google Sheets, complete with a detailed step-by-step process.

And as always, we included detailed screenshots and video guides throughout today’s tutorial. In case you find any of the steps outlined in today’s guide confusing, these resources should make your job easy.

Convert Text to Numbers in Google Sheets – A Step-by-Step Guide