Cell Formatting to except Longitude & Latitude in DMS anD format them

metuckness

New Member
Joined
Sep 4, 2013
Messages
7
Hi,

I hope this is pretty simple. I have been asked to take over address for the county i works for and I am in the process of creating an Excel (2010) template sheet that the Secretary taking the address request calls can fill out information I need to verify and add an address.

I have two cells B33 and E33. B33 is for Longitude. All our reading in our county are going to lead off with -103 Longitude and I believe they all will do the same for 32 as our Latitude cell except it should not be in the negative.

I would like her to take the coordinate she gets always in the format as (Cell B33) -103 19.595 and Cell (E33) 32 58.744.

Instead of her having to actually type (in ta general formatted cell) -103 19.595 I would rather she just be able to type into the cell 103 19.595 and it would for mat the cell to display -103 19.595. The same for the Lat cell thought it should not display in the negative..

It's not really that I want to change the format of the displayed coordinates, but make it easier for her to enter them and they format to what I need. the perfect example is the telephone cell. I have a cell setup to take the reporting persons phone number. Well she just types in 5755559900 and the cell automatically formats it to (575)555-9900.

Could anyone explain how to format that long and latitude to accomplish this? Would be appreciated. So far most I have heard about is how to convert the format to something else or display it like this:

LONG:103°19'35.7"W LAT:32°58'44.64"N

SO what I am using it it for is when I punch in the DMS format into ArcGIS as -103 19.595 & 32 58.744 it converts it to the above and I do a search and bingo, all is good. But since the cell is just a general formatted filed the Secratary has to enter -103 19.595 each call etc.

Thanks!

Mitchell
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use Conditional Formatting with the rule Format only cells that contain Specific Text beginning with 103 and the number format "-"@.
 

metuckness

New Member
Joined
Sep 4, 2013
Messages
7
You can use Conditional Formatting with the rule Format only cells that contain Specific Text beginning with 103 and the number format "-"@.
So where would I find that? If that a Format Cells Custom Option or?

Sorry, I do computer networking and repair, not much into using the programs, just to help others do simple stuff :)

Thank you sir!

Mitch
 

metuckness

New Member
Joined
Sep 4, 2013
Messages
7
I found the location, didn't realize the tool bars were now called Ribbon, but I am still not seeing the connection between the information I have and how I need it displayed.

If I set the cell to Conditional Formatting then Format Only Cells That Contain - Specific Text - Beginning With - then type in 103 ''-''@ doesn't seem to modify the data when I enter it in any noticeable fashion.

I think what your showing me is that when the rule detects a number value of 103 xx.xxx it will display it as -103 xx.xxx is that correct?

But I don't know the entire formula to get that to happen.

 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The "-"@ is the Format not what the text begins with. Click the Format button and, on the Number tab, enter it in the Type box after choosing Custom.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,118
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top