An issue that comes up quite often in Excel is numbers that have been entered or formatted as text values. This can cause many headaches when trying to troubleshoot why a formula like a SUM is not giving the correct answer. Even though the data looks like a number, it can actually be a text value and will be ignored from any numerical calculations like a sum. In this post, I will show you how you can identify such numbers which are stored as text values, and 7 ways you can use to convert the text into a proper number value.
How to Indentify Numbers Entered as Text
How can you tell if your number data has been entered as text? There are quite a few easy ways to tell!
Left Aligned
The first way you might be able to tell if you have text values instead of numerical values is the way the data is aligned in the cell. By default text is left aligned and numbers are right aligned. Unless the alignment formatting has been changed from the default, this will be an easy way to spot numbers that have been formatted as text.
Green Triangle Error Checking
If you don’t realize you have numbers entered as text values, it can potentially cause some serious errors. This is why it’s flagged by Excel’s built-in error checker. Any cells that are flagged with an error will show a small green triangle in the left of the cell to visually indicate an error. When you select such a cell a small warning icon will appear. When you click on this, it will show you what the error is. In this case, you can see the error is Number Stored as Text. You can turn off this error checking entirely, or customize which errors are flagged. Go to the File tab and then select Options at the bottom. This will open up the Excel Options menu. In the Formula section of the Excel Options menu ➜ Error Checking. Uncheck the Enable background error checking option to disable this feature. You can also customize the color used to indicate errors here.
Text Format Applied
One way that numbers get entered as text is when the cell has been formatted as text. This causes values to be entered as text values regardless of whether they are numerical. There is a quick way to determine whether or not a cell has had text formatting applied. Select the cell and go to the Home tab. You will be able to see if Text is the selected formatting in the dropdown menu found in the Number section of the ribbon.
Preceding Apostrophe
Another way that numbers can be entered as text is by using a preceding apostrophe. When you enter an apostrophe ’ as the first character in a cell, anything after will be regarded as a text value by Excel. This means if you see a leading apostrophe in the formula bar, you know the data is a text value.
Use the ISTEXT Function
Excel has a function you can use to test if a value is a text value or not.
input is the value, cell or range which you want to test if it is text.
The ISTEXT function will return TRUE if the value is text and FALSE otherwise. Here you can see the ISTEXT function being used to determine if the cells contain text. The function returns TRUE when it finds a text value.
input is the value, cell or range which you want to test if it is a number.
Similarly, you can test if a value is a number using the ISNUMBER function. It takes a single input and returns TRUE when the value is a number and FALSE otherwise. Here the same data is tested using the ISNUMBER function. The function returns FALSE when it does not find a number.
Status Bar Only Shows a Count
Another way to quickly test a range of cells to see if they are all text is by using the status bar. When you select a range of numerical values, Excel will show you some basic statistics like the sum, min, max, and average in the status bar. If they are all numbers entered as text values, then these basic statistics won’t be calculated. Instead, you will only see the count of the cells in the status bar.
Convert Text to Number with Error Checking
You have already seen that you can use error checking to spot numbers entered as text. The error checking will also allow you to convert these text values into proper numbers. Click on the Error icon and choose Convert to Number from the options. Excel will then convert each cell in the selected range into a number.
Convert Text to Number with Text to Column
Usually, Text to Columns is for splitting data into multiple columns. But with this trick, you can use it to convert text format into the general format. Text to Column will let you choose a delimiter character to split your data based on. If you deselect this option and don’t pick any delimiter then it won’t split your data. You are then able to choose how to format the results, and this is where you can choose a general format for the output. Follow these steps to use the text to column feature to convert text to numbers. This will convert your text values into numerical values. The Text to Column command is usually used to split comma-separated or other delimiter separated data into multiple columns. Any delimited data will be text data, so when Excel splits the data into multiple columns it also converts numerical data into numbers for added convenience. This is also true even if there is nothing to split! So the Text to Column wizard can be used to converts numerical text values to numbers.
Convert Text to Number with Multiply by 1
Even when values are stored as text you can still perform certain numerical calculations with them and the results of these calculations will also be numerical values. You can use this to convert the text into numbers by multiplying them by 1. Since you are multiplying by 1, it won’t change the numbers but it will convert them. You can use the above formula in an adjacent cell and copy and paste it down to convert an entire column. If you want to remove the formula after, you can copy and paste them as values.
Convert Text to Number with Paste Special Multiply
Multiplying by 1 is a great trick for converting text to values, but you might not want to use a formula for this. The great news is there is an easy way to multiply your text by 1 without using a formula. This means you can convert your text in place and you don’t need to copy and paste formulas as values in an additional step. You can use Paste Special Multiply to convert your text values. Follow these steps to multiply by 1 using Paste Special. This will multiply all the values by 1 which was the value in the copied cell. As a result, the text is converted into values, but since you are multiplying by 1 the numbers won’t change.
Convert Text to Number with VALUE Function
There is actually a dedicated function you can use for converting text to numerical values. The VALUE function takes a text value and returns the text value as a number.
text is the text value you want to convert into a numerical value.
If the text contains any non-numerical characters, then the VALUE function will return a #VALUE! error. It doesn’t extract numbers from text, it can only convert numbers entered as text into numbers. You can use the above formula to convert the text in cell B3 into a number and then copy and paste the formula to convert the entire column.
Convert Text to Number with Power Query
Power Query is an amazing tool for any type of data transformation required. It can certainly be used to convert text into numbers as well. Power Query is strongly typed. This means calculations with incompatible data types will result in an error. So you won’t be able to multiply text values by 1 to convert them into numbers. But Power Query does come with an easy way to convert data types, including text to numbers. Follow these steps once your data has been imported into Power Query. Click on the data type icon to the left side of the column heading for the values you want to convert. Each column in the Power Query editor has a data type icon that displays the current data type of the column. If the column has not been assigned a data type, then the icon shown will be ABC123. There are several numeric data types available in Power Query and which one you choose will depend on the level of decimal precision you need. In this case, you can select the Whole Number option from the menu. Notice the values in the column are now right-aligned and the data type icon has changed? These are visual indicators that the data type is now whole numbers instead of text. You will see a new Changed Type step has been applied to the query and it will use the above M code formula. You can then press the Close & Load button in the Home tab to save the results and load the data into Excel. You might not even need to apply this data type conversion step to your query. When you first import data into Power Query, it will usually guess the data type and apply the conversion for you!
Convert Text to Number with Power Pivot
If you want to analyze or summarize your data after you convert it from text to numbers, then you might want to do the conversion in Power Pivot. Power Pivot is an add-in that allows you to efficiently analyze millions of rows in the data model with Pivot Tables. With Power Pivot, you can build row-level calculations using the DAX formula language to create new columns in your datasets. You can then access these new columns in your pivot tables connected to the data model. In this case, the DAX formula you can use to convert text to numbers is the exact same as the formula in the grid. Follow these steps inside the Power Pivot add-in to create a new calculated column that converts the text to numbers. Select any cell under the Add Column heading. Insert the above formula. TextNumbers is the name of the table of data in Power Pivot and Text is the name of the column you want to convert. Double click on the column heading to change the name. Press the Save icon and close the Power Pivot add-in. Now you can create a pivot table from the data model and this new column will be available for use inside the pivot table just like any other field. The calculated column will be available in your pivot table fields list and you will be able to use it like any other number field.
Conclusions
There are many reasons why you might have data that contains numbers formatted as text. If you want to freely use these values in your calculations, you will need to convert them into numbers. Thankfully, there are many easy options to convert text to numbers such as error checking, paste special, basic multiplication, and the VALUE function. These are all easy ways to convert text inside the grid. If you’re importing your data using Power Query or Power Pivot, you can perform the conversion inside each of these tools. Both have methods available to convert text to numbers. Do you use any of these methods to change your text values to numbers? Do you know any other interesting ways to perform this action? Let me know in the comments below!
