What you enter in a Microsoft Excel spreadsheets is read by the program as general data, text, or numeric data. But there are limitations to each, and you obviously have to suit your own needs and preferences while using Microsoft Excel. The cell data type can be interpreted by Microsoft in different ways: numeric, general or text. The interesting thing is that even if you enter numerals you can have the program read the data as text. But why would you want to do that?
As a fact, Microsoft Excel automatically fixes the number that we enter. This is much like Microsoft Word correcting some simple errors as we continue to type. Likewise Excel modifies the numerals so that they make sense. For example if I type in 0210 so Excel would automatically eliminate the first zero since it is meaningless to have that character. But if I want to keep it, I would have to change some settings. What is required in that Microsoft does not read the data as numbers? Once it does not interpret the data as numeric, it would not be bothered about the number sequence and corrections.
The “types” function in Microsoft Excel 2007 is a very useful feature as it allows you to determine and set the cell data type. The data you need to have in an Excel spreadsheet can be all numbers or numbers and letter or maybe just letters. With the type feature you can easily form formulas as a formula consist of both numbers and letters usually. The syntax for the type function that you need to know in order to use is TYPE (value). Here the value can be any type of data entry like a number or text for example.
Now here is how value type is encoded in this way. The following numbers show the corresponding value types.
- 1 is for numbers
- 2 is for text
- 4 is for logical value: which means that the content is in terms of a true or false
- For an error value, the code is 16
- Finally, for a range or array of contents, the value 64 is used.
Converting data types
If the data you receive has numbers written in the form of text you may want to change the data type to make the spreadsheet more bearable! By default the numeric entries are right aligned in the cells. So when you see numbers aligned to the left you can easily tell. At other moments you may want to change a numeric format so that Excel treats it as text. This may be so because if an entry is treated as a number you cannot add a preceding zero as it would vanish automatically. Now if you modify the system such that the numeric entries are being treated as text, first they would be left aligned, and then you know you can add numbers as you please and there is no automatic emission of any zero for example.
Now, here is how set the data type:
- General to text: To change the data from general to text, you only need to add an apostrophe before the number. With an apostrophe Microsoft Excel will read the numeric entries that you then enter as text and you can change them as you please.
- Text to general: To deal with a text data that you want to be read as general data, you only have to click in the cell and simply hit the Enter key. And you are done: your text data will now be read by Excel as a general data.
This free Excel help article is just a basic overview on how you use the basic data types as you work thru the program, and how you keep your data the way you want it to be presented. Basically, the data type is thus decided by you and Excel follows, unless you manually make the change.
If you need help with your Microsoft Excel or Microsoft Access files, be it Excel Consulting or Access Programming, we can help. We have been doing so in the Irvine area of Orange County for decades. In 2004 we started to help other markets, such as Los Angles, San Diego, Long Beach, San Francisco, Oakland and then off to Dallas, New York, Florida, and Manchester. We offer free consultations, if if you need more help, than is offered here, please give us a ring or drop us a line.