If you are working on a Microsoft Excel file from scratch then you will not find any difficulties because you would have intelligently selected the correct data type of the cells, but if you have imported the data from some other type of application or file then the data type of data which gets imported might not match the data type of your file. This results in errors and miscalculations because if your column was suppose to have numeric values and now it has text values then obviously the SUM function run on that column will not yield the correct results. In this post we will let you some ways to correct these errors and data type mismatch.
First of let us understand why this happens and why a number gets treated as text in a cell in Excel? The answer to this is simple; what we type in any cell in Excel is the Value and the way it gets represented and saved for calculations depends upon the data type of that cell. For example right click on any cell and go to its Formatting options. There set the type to Text. Now enter a date in the cell. You will find that though you entered the date in mm/dd/yyyy format but still it gets displayed as a plain number. This happens because the data type of that cell is Text and it is changing the date to text.
Convert using VALUE function:
In Excel there is a Function called VALUE which can be used to convert the text type field to a number type. Try using this function for making the changes in the values you want.
Convert Using the Error Sign:
Cell in which Excel find some data mismatch it notifies you about the same using a green mark on the upper left corner. If you move your mouse pointer over this notification you get some options and one of these options is to convert to number. This can be used to convert the mismatched Text to Number. This method is however slow as you need to do it for every cell individually.
Use TRIM function:
At times after importing or while typing it happens that spacebar gets pressed and a space character gets inserted along with the numbers. Presence of a single alphabetic character in a number makes it a text item. This can be rectified by using the TRIM function available in Excel. TRIM function remove all the spaces from any cell and thus will result only in numerical data.
Excel has inbuilt Data Validation tool which if set check the type of data being entered in any cell and warns or disallows you to enter text type data in a cell which should accept only numbers. This tool is under the data ribbon and can be used for avoiding such issues.