Join Dennis Taylor for an in-depth discussion in this video Avoid unintentional entries when typing code numbers, part of Excel Tips Weekly.
- [Instructor] When you type certain kinds of numbers, perhaps a code number, product number, part number, ID number, if they look something like, what we're seeing in this green box, you might get some unintended entries, I'm gonna type one mar, now whether I type upper or lower case, c six seven, just the way we see it in that first entry, I press enter, and I'm a little bit surprised, what do we see here? Excel reads that as March 1st, 1967, how about the next one, nove seven? Whether I use upper or lower case makes no difference but here too, because we have a partial spelling of a month, Excel reads that as if it were a date, November 7th, 2018, it's the current year and so on, so the first four of these, what they all have in common is you'll see at least three letters of a month here, now it might be four letters, it could be five or six, same thing will happen here, dec two three four five, and what'll we get out of this, certainly doesn't look like a date as we're typing it, that's December 1st in the year 2345, now, seven three four five dash ten, maybe that's an ID number, I'll try that one, dash four, maybe those first four characters have something to do with a location number, next two numbers after the dash have something to do with a state, who knows what these would mean, but if it's a coding scheme like this, press enter, what do we get here, that's October 4th in the year 7345, so any kind of a coding structure that might be using something like we're seeing here, potentially runs the risk of being misinterpreted, now there's an easy fix in general, and that is, to format these cells ahead of time as text, I'm going to do this for the entire column B, simply click there, and on the home tab, you'll see the option, general, right here, click the arrow to the right of it, choose text, I won't type all these over again, but here's the first one, one mar six seven, looks like that, ideally, with codes, the letters are looking better if we type upper case, so I could do that here, I'll do the next one, NOVE seven, but this time it's not being interpreted, and it's not because I'm just typing upper case either, it's because this column and the cell in particular, of course, has been formatted as text, so that's certainly the work-around, for those kinds of entries, there's another surprise from time-to time, if the letter e is in some kind of a coding structure, depending upon the location of the e and what follows it, you might be entering something completely different, and if you're not familiar with what we're about to see, it really throws you, so I'm about to type two four seven e three, as I press enter, what do we get? What is that? Well, you may or may not be familiar with engineering code, what this means is two point four seven times 10 to the 5th power, in the formatting bar up above you do see that that's the value 247 thousand, who would ever have guessed that? Of course, you wanna be careful with any kind of a coding structure that has an E in it like that, how about the entry five slash eight, five eights, I press enter, I wasn't thinking of May 8th of the current year, but that's exactly what I get, if you want to type a fraction, and have the actual value appear in the cell, equal five slash eight, mow the real value of that is point six two five, and that's what we get, if you'd rather see the fraction displayed, you can jump in to format cells, control one is a fast way, and then on the number tab choose fraction, if you're gonna be having other entries in the same column, you might think about whether it's gonna be one digit, two digits, three digits, no matter which of these we choose here for the specific example, makes no difference, we will see this being displayed as five eights, and just to check that out further, if you wanted to multiply it by something, let's multiply it by eight, if it truly is a value, and it is, we will get an answer here that makes sense, five eights time eight is five so, so we've seen different situations, where any time you're using either three, four, or five, six letters, any kind of a spelling of a month, from three onward, possibly embedded in a code, being prepared at different times for unusual entries, and so the way to avoid these as we suggested, is format the cells first as text, and then add the entries, now I didn't do the one here, two four seven E three because the column is formatted as text, that will work properly too.
Author
Updated
2/19/2019Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Excel 2016: Tips and Tricks
with Dennis Taylor5h 51m Beginner -
Visio Tips and Tricks
with Scott Helmers1h 49m Intermediate
-
Excel Tips - New This Week
-
Create picture links New5m 37s
-
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an Organization Chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Avoid unintentional entries when typing code numbers