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:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You can use Conditional Formatting with the rule Format only cells that contain Specific Text beginning with 103 and the number format "-"@.
 
Upvote 0
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
 
Upvote 0
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.

excel-1.jpg
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top