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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we're going to talk about leading zeros and the reason we're to talk about this is, I got a couple of emails of people saying.
Hey you didn't put me on the map.
I sent my entry in for the podcast 700 thing and I'm not there.
And I went checked that I had their email, and I went checked the file and they were in the file.
You know and when I did this matching it went through and said great, we matched all the records, but that's not true it turns out.
Here we have MapMeUSA I'm going to double click here, and it actually says.
Hey, I completely skipped 41 records.
You skipped 41 records? Why didn't you tell me that and it turns out when I go down here, it says yeah, you got a whole bunch of records without any zip code information at all.
So I went back to the original workbook and frequently When I have your zip codes, I know there's leading zeros up in new England, so I always format those as text and sure enough right here around row 68, we had a whole bunch of text zip codes.
I did that to keep the leading zero.
Well it turns out what we have to do is we have to convert those text values to regular values.
Fast way to do that. "Data" "Text to Columns" "Finish", and then if you want the leading zeros to show up we use "Format Cells" and there's a special format called zip code.
Now this is really just basically a way of creating a format that is 5 zeroes. Does the exact same thing. So once I reimportthis data, sure enough there are a whole bunch of additional records up here in new England, that were not included before.
So this is the actual map. Now the good news is you were in the drawing because you were in the excel file.
This Five-digit zip code. We'll take a look at the custom number format.
If we click custom, it's simply a matter of putting in five zeros.
And it reminded me of a problem that I frequently have that I always take the long way to solve.
Here we have some lesson numbers from Lesson 1 up through lesson 115 or so and I want to create a consistent file naming structure for the excel file that goes with those lessons and frequently what I do is I come here, and I do a concatenation =LESSON and I want to have leading zeros, so it's LESSON001.
That way it'll sort correctly in windows explorer and so what I do is I end up using the right of a couple of zeros ampersand the lesson number , 3 so what this does is, it will take for example for lesson 115, I'll end up with 00115 and the right three digits as 115 but for lesson 1 it will be 001 and the right three digits are 001.
&.xls So there we can create a nice file name and copy that down and that's usually the method that I use.
But it turns out that that really is going through a whole lot of extra work, and what we could do instead is take ="LESSON" and then use the text function.
The text function says take that value over there in A1 and format it in the 000 format &.xls and very quickly without having to use the right function without having to append those extra zeros, we come up with leading zeros for all of our records.
Well hey there you have it.
Sorry to everyone who is not included in the original map.
Good news is you were in the drawing and we learned something about leading zeros, problems with mappoint and text zip codes and also a cool way to make sure that you're leading zeros show up when you can catenate text.
Well thanks for stopping by. Will see you next time for another netcast from MrExcel.
Today we're going to talk about leading zeros and the reason we're to talk about this is, I got a couple of emails of people saying.
Hey you didn't put me on the map.
I sent my entry in for the podcast 700 thing and I'm not there.
And I went checked that I had their email, and I went checked the file and they were in the file.
You know and when I did this matching it went through and said great, we matched all the records, but that's not true it turns out.
Here we have MapMeUSA I'm going to double click here, and it actually says.
Hey, I completely skipped 41 records.
You skipped 41 records? Why didn't you tell me that and it turns out when I go down here, it says yeah, you got a whole bunch of records without any zip code information at all.
So I went back to the original workbook and frequently When I have your zip codes, I know there's leading zeros up in new England, so I always format those as text and sure enough right here around row 68, we had a whole bunch of text zip codes.
I did that to keep the leading zero.
Well it turns out what we have to do is we have to convert those text values to regular values.
Fast way to do that. "Data" "Text to Columns" "Finish", and then if you want the leading zeros to show up we use "Format Cells" and there's a special format called zip code.
Now this is really just basically a way of creating a format that is 5 zeroes. Does the exact same thing. So once I reimportthis data, sure enough there are a whole bunch of additional records up here in new England, that were not included before.
So this is the actual map. Now the good news is you were in the drawing because you were in the excel file.
This Five-digit zip code. We'll take a look at the custom number format.
If we click custom, it's simply a matter of putting in five zeros.
And it reminded me of a problem that I frequently have that I always take the long way to solve.
Here we have some lesson numbers from Lesson 1 up through lesson 115 or so and I want to create a consistent file naming structure for the excel file that goes with those lessons and frequently what I do is I come here, and I do a concatenation =LESSON and I want to have leading zeros, so it's LESSON001.
That way it'll sort correctly in windows explorer and so what I do is I end up using the right of a couple of zeros ampersand the lesson number , 3 so what this does is, it will take for example for lesson 115, I'll end up with 00115 and the right three digits as 115 but for lesson 1 it will be 001 and the right three digits are 001.
&.xls So there we can create a nice file name and copy that down and that's usually the method that I use.
But it turns out that that really is going through a whole lot of extra work, and what we could do instead is take ="LESSON" and then use the text function.
The text function says take that value over there in A1 and format it in the 000 format &.xls and very quickly without having to use the right function without having to append those extra zeros, we come up with leading zeros for all of our records.
Well hey there you have it.
Sorry to everyone who is not included in the original map.
Good news is you were in the drawing and we learned something about leading zeros, problems with mappoint and text zip codes and also a cool way to make sure that you're leading zeros show up when you can catenate text.
Well thanks for stopping by. Will see you next time for another netcast from MrExcel.
