This post is going to show you how you can find numbers entered as text values and convert them to numbers in Google Sheets.

You might run into issues where the numbers in your dataset are formatted as text values.

Unfortunately, identifying this can be tricky.

When numbers are formatted as text without you knowing about it, that’s can be a frustrating experience.

Luckily, you have this article to help prevent any frustration.

In this post, you will learn how to check the formatting of your cell contents, and also how to convert numbers formatted as text to numeric values.

Get your copy of the example workbook to follow along.

How to Tell When a Number is a Text Value

Before you use any of the techniques to convert text to numbers, you first need to know that the numbers are in fact not numbers.

The following methods will help to check the formatting of the numbers in your dataset.

Check for Text with Alignment

This method is the quickest and easiest means by which you can tell the cell formatting of your data.

By default, texts contents are aligned to the left while numeric values are aligned to the right.

This allows you to tell at a glance whether or not the cells have the correct formatting.

All you have to do is look at your cells and check for the alignment of their contents.

However, it’s important to mention that you can only rely on this method insofar as you don’t change the alignment setting in your spreadsheet.

If you’ve changed the alignment format, you can refer to any of the following methods.

Check for Text with the SUM Function

= SUM ( value1, [value2, …] )

The SUM function will return the aggregate of numeric values or the values in a cell range.

But when the SUM function is used on a range of text values it returns 0. This way you know the numbers are not in the right format.

Check for Text with the Status Bar

For this method, select the cells and check the type of operation carried out in the status bar.

If it shows a Count as it does in the image above, then you know the numbers are formatted as text.

When the contents are formatted as numbers, the status bar will show a Sum for the numbers in the range.

Check for Text with the ISTEXT or ISNUMBER Function

The previous two methods discussed suffer from a crucial flaw.

When the selected cell range contains both text and numeric values, they will ignore the text contents and return the sum of the numeric values in the range.

For instance, if only one cell out of 200 cells has a number formatted as text then you will not spot it as the sum will ignore the text values and treat them as zero.

The calculations you carry out will be wrong, and that can seriously affect the outcome of your analysis!

You can avoid all of this messy situation by using either one of the ISTEXT or ISNUMBER functions. They will give you a fool-proof test regarding the type of formatting present in each cell.

= ISTEXT ( value )

ISTEXT checks whether a value is a text string.

= ISNUMBER ( value )

ISNUMBER checks whether a value is numeric.

= ISTEXT ( A2 )

Enter the above function syntax into a new cell. Then use the fill handle to copy down the formula to other cells.

= ARRAYFORMULA ( ISTEXT ( A2:A6 ) )

Alternatively, you can use place the function in an ARRAYFORMULA like above.

This will return TRUE for any cell which contains text values.

= ISNUMBER ( A2 )
= ARRAYFORMULA ( ISNUMBER ( A2:A6 ) )

You can also follow the same process to use the above ISNUMBER syntax.

This will return FALSE if the number is a text value.

The only drawback with this method is that you have to create a new column of redundant data. Nevertheless, you will be certain of which cells have numeric content and which have text values.

Now that you know how to identify the type of formatting in a cell, you might want to change these text characters to numeric values.

Convert Text to Numbers by Multiplication

There is a very easy way to convert text values to numbers, you simply have to multiply by 1. This will force the results into a number.

= A2 * 1
= MULTIPLY ( A2, 1 )
= ARRAYFORMULA ( A2:A6 * 1 )
= ARRAYFORMULA ( MULTIPLY ( A2:A6, 1 ) )

In a new cell, copy and paste any of the above formula syntaxes.

The values will become numeric. You will be able to confirm the conversion by the obvious right alignment of the cell contents.

Convert Text to Numbers with Format Menu

The Format menu contains options that you can use to change cell format. This means you can change the format from text to number.

Select the cells with the contents you want to change their format.

1. Go to the Format menu
2. Select the Number option.
3. Select Number.

When you complete the process, the contents of your cells should be right-aligned indicating they’re now numeric values.

Instead of using the Number format, you can create a custom format.

While your cells are selected, go to the Format menu and click on Numbers. Then select Custom number format.

In the dialogue box that opens, copy the formatting instruction into the search bar and click Apply.

Convert Text to Numbers by VALUE Function

Another function you can use to change the text to numbers is the VALUE function.

= VALUE ( text )

The VALUE function takes a text string and converts it to a numeric value. It’s the perfect function for the purpose that you want to achieve.

= VALUE ( A2 )
= ARRAYFORMULA ( VALUE ( A2 ) )

Copy and paste any of these formula syntaxes into a new cell. The result will be the conversion of the text string into numeric values.

Convert Text to Numbers with an APPS SCRIPT

The apps script allows you to create custom functions and menu commands that meet your needs.

You can access the apps scripts editor by going to the Extensions menu and selecting the Apps Script option.

Check out the guide to Apps Scripts in Google Sheets for more information on this powerful tool.

function numberFormat() {
  SpreadsheetApp.getActive().getActiveRange().setNumberFormat("####.00");
};

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Number')
    .addItem('Change to Number Format', 'numberFormat')
    .addToUi();
};

In your code editor window, copy and paste the above code syntax. Click on Save, then Run. Grant the necessary permissions and refresh your spreadsheet window.

After you refresh your spreadsheet, the syntax will create a new menu item labeled Number.

In this custom menu item, you will find the submenu Change to Number Format. This command will convert the text to numeric values.

The script essentially uses a Custom number format using the setNumberFormat("####.00") method to convert the text to numbers.

Conclusions

The processes described above show the importance of having your numeric cell contents in the correct number format.

Now you can easily identify and apply the appropriate fixes to change any text values to numbers.

Using the MULTIPLICATION and VALUE functions to change the format provides you with a dynamic approach. You can embed them into larger functions to carry out more operations at once.

But adding new columns can increase the file size of the spreadsheet, thereby reducing the speed at which it responds.

The Format menu and apps script option doesn’t require you to create redundant data by creating extra columns. You can use these to directly change the source data.

Do you know of any other method for changing text to numeric format? Let me know in the comments below!