Excel - Lat 12 34.567: Episode 1475
623 views · Published 22 November 2011 · 2:30 · Indexed 1 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: Converting 7-Digit Numbers to Lat/Long Format in Excel - Custom Formatting. Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing a question that was posted on the MrExcel Facebook page. The person asked how to convert a column of 7-digit numbers into the format Lat 12 34.567. At first, I thought this would require some complex formulas, but it turns out we can easily achieve this using custom formatting in Excel. To begin, we will select the column of numbers and go to the Format Cells option. Alternatively, you can use the shortcut Control+1. From there, we will go to the Custom tab and build our custom format. The first thing we want to include is the text "Lat" followed by a space. We can do this by putting the text in quotes. Next, we want to include the first two digits, followed by a space. To do this, we can use either a backslash or a space. Then, we want to include a period in quotes, followed by the last three digits. This will give us the desired format of Lat 12 34.567. But what about the longitude column? Well, that's an easy fix. We can simply edit the custom format to say "Lon" instead of "Lat" and apply it to the longitude column. This is a great example of using custom formatting as a facade, where we are not actually changing the underlying numbers, but just how they are displayed in Excel. Shout-out to Mike, author of "Slaying Excel Dragons", for coining this term. I hope you found this episode helpful and learned something new about custom formatting in Excel. Don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching and we'll see you next time for another episode of the MrExcel podcast. 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/ Table of Contents: (00:00) Converting Numbers to a Cryptic Format (00:18) Using Format Cells to Convert Numbers (00:28) Building a Custom Format (00:40) Explanation of Custom Format (01:14) Editing Custom Format for Different Column (01:39) Applying Custom Format to Other Numbers (01:58) Facade Formatting (02:18) 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: Convert numbers to custom format Custom format cells in Excel Excel formatting Excel podcast 1476 Facade formatting in Excel Facebook question on number formatting Formatting numbers in Excel Lat 12 34.567 format Lon column formatting MrExcel podcast episode Slaying Excel Dragons book by Mike Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153153/ 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!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920