forcing to enter a specific format of numbers and symbols in a cell

[sjoerdbosch]

I am looking for a way - preferably hidden in the sheet as a macro - to force users to enter a specific format for latitude and longitude (2 different cells)
The format must be: 00-00.0 N/S - where the 1st 2 digits max 90 separated by - and then a max of 99 separated by . [not comma] and the last digit max 9 followed by a space and N/S should be a choice for North or South
The cell below the latitude is the longitude - 000-00.0 E/W - where the 1st 3 digits is max 180 followed by - and then again the same as latitude and the E/W is a choice for East or West

I have tried data entries and conditional formatting - and some silly macros - none of them work

Please help

 

[bebo021999]

My decimal setting is "." and I am testing with this.
How about yours? I guess ","?
Try to set into "." then test again?

 

[sjoerdbosch]

hi. sorry for late reply. Very busy at the moment
But - No. Really I have set excel to the proper settings for the separators and if I type the "," it is entered as 16-32,2 N and if I use the "." the cells is emptied and the box appears
I have tried several PC's and it is the same on all of them.
Really frustrating because as far I can see the macro is written correctly and you say it is working on your side. So, I do not know.

Maybe a separate macro for lat and for long ?
Also, N/S/E/W should be in upper case. I made a separate macro for that before but I have removed when entered your macro
Would be better if it inside the macro you wrote

Appreciated your attention and help btw

 

[sjoerdbosch]

Anyone else who can assist?