INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...The level of expertise is awesome. The nature in which people respond is professional helpful and not the least condescending. I can't say that for most forums..."
Geography
Where in the world do Tek-Tips members come from?
|
Best of Excel
|
When is a NUMBER not a NUMBER?
Posted: 4 Jun 07
|
Numbers are values that we can do arithmetic operations to.
However, there are values that appear to be numbers, that, in reality, are not numbers. If, for instance, you have a value that needs leading zeros, because the length of the value is always the same, like a USPS ZIP Code, you are not working with a number; rather you have an IDENTIFIER.
IDENTIFIERS are values that can contain ALL numeric characters, but 1) you will NEVER use these values mathematically, 2) most often they have a defined length and 3) they are used to describe some entity. ZIP Codes, Invoice Numbers, Part Numbers, Phone Numbers, Social Security Numbers, Employee Numbers are some examples of IDENTIFIERS as opposed to NUMBERS, and, as such, need to be stored as TEXT values rather than NUMERIC values.
Why is this distinction important? Computers store NUMBERS in a significantly different way than TEXT. Computers manipulate NUMBERS in a significantly different way than TEXT. Have you ever had a column of TEXT and NUMBERS that you tried to SORT in Excel? Excel asks you how you want the values to be handles, because it can't figure it out without your input. If you do a QUERY on a column containing TEXT and NUMBERS, you will be sadly disappointed with the results.
Excel gives you some "help" by offering Numeric Formatting for ZIP Codes, Phone Numbers and Social Security Numbers. I AVOID this "help" because I consider these type of values TEXT and not NUMBERS. By doing so, I avoid ambiguities and confusion in my downstream processing.
What to do:
Convert numeric IDENTIFIERS to TEXT using the TEXT function.
If you are using Data > Import External Data > Import or Data > Text to columns, always choose TEXT, rather than GENERAL, for columns that contain IDENTIFIERS.
|
Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum
|
|
 |
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
