Look Who it Was Random video

Excel - Preserving Leading Zeros in Excel: Avoid Data Loss & Ensure Accuracy - Episode 721

630 views · Published 13 February 2009 · 4:22 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Preserving Leading Zeros in Excel: Avoid Data Loss & Ensure Accuracy.

Welcome back to the MrExcel netcast! In today's episode, we're going to dive into the topic of leading zeros. This may seem like a small and insignificant issue, but it can actually cause some major problems, as I recently discovered when I received a few emails from viewers who were not included in our MapMeUSA project. After some investigation, I found out that the issue was due to leading zeros in their zip codes.

So why do leading zeros matter? Well, when we import data into Excel, it automatically converts numbers to a general format, which means that any leading zeros are dropped. This can be a problem when dealing with zip codes, as some areas, like New England, have leading zeros in their zip codes. To avoid losing this important information, I always format zip codes as text. However, this can cause issues when trying to match data, as I discovered when I realized that 41 records were skipped in our MapMeUSA project.

To fix this issue, we need to convert the text zip codes to regular values. The fastest way to do this is by using the "Text to Columns" feature and then formatting the cells as zip codes. This will ensure that the leading zeros are preserved. After reimporting the data, we were able to see the missing records on our map. So if you ever encounter a similar issue, remember to check for leading zeros in your data and convert them to regular values.

But leading zeros aren't just important for data matching, they can also be useful when creating consistent file names. For example, if you have a series of lessons numbered from 1 to 115, you may want to add leading zeros to ensure that they are sorted correctly in Windows Explorer. In the past, I would use the RIGHT function to add the necessary zeros, but I recently discovered a much simpler method using the TEXT function. This allows you to format a number with leading zeros without having to use any additional functions.

So there you have it, a quick lesson on the importance of leading zeros in Excel. I hope this video has been helpful and has taught you something new. And to those who were not included in our MapMeUSA project, don't worry, you were still in the drawing and now you know how to avoid this issue in the future. Thanks for watching and don't forget to tune in for more helpful tips and tricks in our next netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Many people were missing from the map on last Monday's podcast. Did I miss their entries? No! I use a common Excel trick to keep leading zeroes, but this confused MapPoint. In today's podcast, we take a look at other ways to keep leading zeroes in Excel. Episode 721 shows you how.

Table of Contents:
(00:00) Introduction
(00:13) Leading zeros and the reason for discussion
(00:26) Issue with missing entries on map
(00:36) Solution using "MapMeUSA"
(01:00) Converting text values to regular values
(01:23) Fast way to convert using "Data" and "Text to Columns"
(01:51) Additional records found in New England
(02:02) Custom number format for five-digit zip codes
(02:20) Solution using concatenation and the "right" function
(03:03) Alternative solution using the "text" function
(03:44) Recap and apologies for missing entries on map
(04:01) 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:
Concatenation
Custom number format
File naming structure
Format Cells
Leading zeros
Lesson numbers
MapMeUSA
Mappoint
Podcast 700
Text function
Zip code information


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152172/

More from this channel