change nember set into coordinates

[Do]

I have data sets coming to me in an odd format. Latitude is formed like:
41-22-40.
Longitude is formed like: 104-58-15.

I would like to change the data into useable coordinates. I need latitude
to be formed:
4.22.40 (basically change the dashes to decimals). I need the same thing
for longitude
but I need it formed: -104.58.15. Again, change the dashes into decimal,
but I also
need it to be negative.

Thanks

 

[isabelle]

hi,

do you want to do this with a macro (vba)?
is the latitude and longitude are listed in different columns?

isabelle


Le 2013-11-06 18:18, (e-mail address removed) a écrit :

 

[Norman Jones]

I have data sets coming to me in an odd format. Latitude is formed like:
41-22-40.
Longitude is formed like: 104-58-15.

I would like to change the data into useable coordinates. I need latitude
to be formed:
4.22.40 (basically change the dashes to decimals). I need the same thing
for longitude
but I need it formed: -104.58.15. Again, change the dashes into decimal,
but I also
need it to be negative.

Thanks

Hi!

As this would appear to be an ongoing requirement, try placing an
ActiveX command button on the sheet that contains the raw data,
double-click the button and paste the following code into the sheet's
code module which will then be open:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim rCell As Range

Set rng = Selection
For Each rCell In rng.Cells
With rCell
If Left(.Value, 1) = "-" Then
'no action required
Else
If .Value Like "*-*" Then
.Replace What:="-", replacement:="."
.Value = "-" & .Value
.Value = .Value
End If
End If
End With
Next rCell
End Sub

Hit Alt-F11 to return to the worksheet.

to replace the dash format and add an initial minus, select the required
data and press the command button.

===
Regards,
Norman

 

[Ron Rosenfeld]

I have data sets coming to me in an odd format. Latitude is formed like:
41-22-40.
Longitude is formed like: 104-58-15.

I would like to change the data into useable coordinates. I need latitude
to be formed:
4.22.40 (basically change the dashes to decimals). I need the same thing
for longitude
but I need it formed: -104.58.15. Again, change the dashes into decimal,
but I also
need it to be negative.

Thanks

Latitude (in A1): =SUBSTITUTE(A1,"-",".")
Longitude (in A2): ="-"&SUBSTITUTE(A2,"-",".")