Microsoft Excel Tutorial: How to Keep Leading Zeroes in Employee ID Lists.
Welcome back to the MrExcel netcast! I'm Bill Jelen and I want to thank all of you who have called in with questions for the netcast. If you haven't yet, feel free to leave a message on my voicemail at 866-581-0221. As you may know, the podcast started out by going through my book "Learn Excel from MrExcel" and we've covered almost all 277 items in the book. Now, I need your help to come up with new ideas and questions for the netcast.
Today, we have a great question from Chris about keeping leading zeroes in Excel. Chris's job involves comparing sections of employee ID lists using the VLOOKUP function. However, when entering the data, Excel removes the leading zeroes in the ID, causing problems with the comparison. Chris found a workaround by formatting the list as a 5-digit zip-code, but what if the employee ID is not always 5 digits long? How can we keep leading zeroes for 2, 3, 4, or 6-digit lengths?
There are a couple of ways to go about this. The first method is to use an apostrophe before the number, such as '00123. This tells Excel to treat the number as text and it will keep the leading zeroes. However, this can be frustrating if you have a lot of employee IDs to enter. The other method is to pre-select the range and format it as Text. This will also keep the leading zeroes, but be aware that this can cause issues with formulas as they will be treated as text instead of being evaluated.
Personally, I recommend using the apostrophe method for individual cells and converting them to Text, or formatting the entire range as Text if the apostrophe is too frustrating. And if you happen to have an employee ID that is 5 digits long, the zip-code format is a great way to force the leading zeroes to be there. This is especially useful for US zip-codes, where some start with a 0. Thank you for the great question, Chris! If you have a question for the netcast, don't hesitate to call us at 866-581-0221. See you tomorrow for another Excel 2007 Thursday! Thanks for tuning in.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
Table of Contents:
(00:00) Introduction
(00:11) Call for questions
(00:21) Leave a message on voicemail
(01:01) Problem with leading zeroes in employee ID list
(01:14) Solution using zip-code format
(01:24) Alternative solution using apostrophe
(02:01) Another solution using text formatting
(02:49) Recommendation to use apostrophe
(03:09) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Converting numbers to text in Excel
Excel VLOOKUP function
Format entire range as Text in Excel
Formatting cells in Excel
Formatting employee ID as Text in Excel
Gotchas of text formatting in Excel
How to keep leading zeroes in Excel
Importance of leading zeroes in zip-codes
Keeping leading zeroes in different digit lengths
Special category in Excel formatting
Using apostrophe to keep leading zeroes in Excel
Using zip-code format in Excel
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
Have you ever tried to enter an ID number that starts with zeroes? Excel automatically removes the leading zeroes, making it impossible for your VLOOKUPs to work. Rather than resorting to using a Zip Code format, try one of the tricks in Episode 392 to make Excel keep the leading zeroes.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
