A Facebook posting asks how to convert a column of 7 digit numbers (ie, 1234567) into the format of "Lat 12 34.567". The answer is easier than you might think. In Episode #1476, Bill shows us a non-formula way to answer the question and get the result.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1476, Lat 12 34.567 that's a cryptic name for a podcast as I got this question posted at the MrExcel Facebook page person says, he has a column of numbers that have 7 digit each and he wants to convert them into this format, Lat 12 34.567.
I said wait a second don't think i have to do anything here we can just go in to format cells.
So, number click this little dialog launcher here or control+one and we're gonna go to custom, and let's build this custom format.
So, I can see it.
First of, he wants Lat space.
So, that's just going to be in quotes Lat nice anything in quotes that's nice things you do, then we wanna put digit digit and then another space.
So now, to do is face we can go in quotes we can do a space or we can do backslash space either one of those who put a space in and we want 00 .000 and you see that is not doing what we need.
So, that decimal point has to be in quotes and then 000 there we go.
So, there's the sample there you can kind of see as you build it.
So, what's this doing, this is saying hey, in quotes we want Lat space and then the first two digits and then a backspace, space says put a space there and in the next two digits and then in quotes a period and then the last three digits and let's click OK, and you see that it works throughout he also had another column.
Of course up longitude that would be easy enough just go in and edit this custom format, to be Lon and we can without doing any formulas whatsoever change the appearance of those numbers.
Now, in Mike's book Slaying Excel Dragons, he calls this formatting as facade, this is you know the number we're not a storing Lon and there we really just have a number but we put this facade over the top of it to make Excel display, it's different like first book ever saw that used the words facade.
I was pretty impressed with that so, shout-out to Mike.
If you're watching this thanks for a cool term that i'm gonna...
All right, I wanna to thank you for stopping by.
We'll see you next time for another netcast MrExcel.
Learn Excel form MrExcel podcast episode 1476, Lat 12 34.567 that's a cryptic name for a podcast as I got this question posted at the MrExcel Facebook page person says, he has a column of numbers that have 7 digit each and he wants to convert them into this format, Lat 12 34.567.
I said wait a second don't think i have to do anything here we can just go in to format cells.
So, number click this little dialog launcher here or control+one and we're gonna go to custom, and let's build this custom format.
So, I can see it.
First of, he wants Lat space.
So, that's just going to be in quotes Lat nice anything in quotes that's nice things you do, then we wanna put digit digit and then another space.
So now, to do is face we can go in quotes we can do a space or we can do backslash space either one of those who put a space in and we want 00 .000 and you see that is not doing what we need.
So, that decimal point has to be in quotes and then 000 there we go.
So, there's the sample there you can kind of see as you build it.
So, what's this doing, this is saying hey, in quotes we want Lat space and then the first two digits and then a backspace, space says put a space there and in the next two digits and then in quotes a period and then the last three digits and let's click OK, and you see that it works throughout he also had another column.
Of course up longitude that would be easy enough just go in and edit this custom format, to be Lon and we can without doing any formulas whatsoever change the appearance of those numbers.
Now, in Mike's book Slaying Excel Dragons, he calls this formatting as facade, this is you know the number we're not a storing Lon and there we really just have a number but we put this facade over the top of it to make Excel display, it's different like first book ever saw that used the words facade.
I was pretty impressed with that so, shout-out to Mike.
If you're watching this thanks for a cool term that i'm gonna...
All right, I wanna to thank you for stopping by.
We'll see you next time for another netcast MrExcel.
