GPS conversions

[shampoo]

Help! Inexperienced with Excel.

Need Excel 2007 formula(s) to convert longitude/latitude coordinates such as "N48-38-46.5" and "E088-19-14.4" into Degrees Minutes decimal format.

All longitudes are N, and all latitudes E. Long/lat have separate columns.

The N's and E's are followed by one space; I need those N's, E's, and spaces gone. All longitudes (all E) have a zero placemarker after E, which I assume needs to go, too.

Thank you!

 

[WhoCanDo]

"N48-38-46.5" and "E088-19-14.4"

Road to Mashina khabakh. That's a dead end. What do you want to go there for?

But seriously, what is the format you want "N48-38-46.5" and "E088-19-14.4" to be in?

Is is N 48°38'46.5" etc. or something else?

 

[shampoo]

"N48-38-46.5" and "E088-19-14.4"

Road to Mashina khabakh. That's a dead end. What do you want to go there for?

But seriously, what is the format you want "N48-38-46.5" and "E088-19-14.4" to be in?

Is is N 48°38'46.5" etc. or something else?

The current format is Decimal Minute Second (DMS) with some dashes, etc. thrown in. That needs to change to Decimal Degree format (DD).

I have found online converters for individual entries yielding 48.64625, for example, when I enter 48 38 46.5 into separate boxes for decimal, minute, and second.

So, the reformulated entries should look something like 48.64625

If I need to, I could cut out the extra junk (N,E,space,zero) with whatever function needed and then use some kind of GPS function if it exists. Whatever those functions are, that's what I need to know.

 

[WhoCanDo]

So that's

48 + (38/60) + (46.5/3600)

Do you need a VBA macro or cell formula?

 

[benlawson]

I know this is a little untidy, but it works for me:

Northings in Column A, Eastings in Column B

To convert northings use:

=SUM(RIGHT((LEFT(A2,3)),2),RIGHT((LEFT(A2,6)),2)/60,RIGHT(A2,4)/3600)

To convert eastings use:

=SUM(RIGHT((LEFT(B2,4)),3),RIGHT((LEFT(B2,7)),2)/60,RIGHT(B2,4)/3600)

Explanation: the LEFT function within excel looks only at the first n characters specifed in the formula LEFT(string,number of chars), right is similar but it looks only at the last n characters, so by using the two in conjuction with each other you can find the part of the string required.

I have had this problem myself in the past...

Cheers,

Ben

 

[shampoo]

So that's

48 + (38/60) + (46.5/3600)

Do you need a VBA macro or cell formula?

I need a cell formula that I can enter once for each column, rather than having to go through each separate cell manually.

I'm not really sure how to implement the code the above poster offered. It seems specific to individual cells, which will still require a lot of time, unless I'm mistaken. I'm very new to excel.

At this point, I have removed all the N's, E's, and hyphens myself. I have cells in DDMMSS.s format. Is there a code I can use to apply the conversion formula to each column?

 

[benlawson]

I'm not really sure how to implement the code the above poster offered.

Firstly, the formulas I had put up were in column C and D in my sheet, and Column A had the northings and Column B the Eastings.

Since you now have DDMMSS.s you can use the following formula (broken down into parts), Change any reference to Cell to the cell the data is in, eg. A2:
Step 1 - Remove DD: LEFT(CELL,2)
Step 2 - Extract MM:
Part 1 - Obtain DDMM: LEFT(CELL,4)
Part 2 - Remove DD: RIGHT(formula pt1,2)
Step 3 - Convert from DMS to DD {the minutes part} (divide by 60): formula s2pt2/60 = RIGHT(LEFT(CELL,4),2)/60
Step 4 - Retrieve last 4 characters (SS.s): RIGHT(CELL,4)
Step 5 - Convert from DMS to DD {the seconds part} (divide by 3600): formula s4/3600 = RIGHT(CELL,4)/3600
Step 6 - Sum results from Step 1, Step 3 and Step 5.
Resulting Formula:

=LEFT([I]CELL[/I],2)+RIGHT(LEFT([I]CELL[/I],4),2)/60+RIGHT([I]CELL[/I],4)/3600

I had it in a =SUM() before rather than listed out with +'s. Hope you can understand the process a bit better now.

The LEFT function extracts a specified number of characters from the left of a string, the RIGHT function extracts from the right.

Cheers,
Ben

 

[WhoCanDo]

=LEFT([I]CELL[/I],2)+RIGHT(LEFT([I]CELL[/I],4),2)/60+RIGHT([I]CELL[/I],4)/3600

How you going with this shampoo? Do you need any extra help?

 

[WhoCanDo]

Just had a thought, since the decimal place is variable in length then maybe this would be the best option:

=LEFT(CELL,2)+RIGHT(LEFT(CELL,4),2)/60+RIGHT(CELL,LEN(CELL)-4)/3600

 

[shg]

Easier to do this in two steps.

      -----A------ ----B---- -----C-----
  1   S48-38-46.5  -483846.5 -48.6463889
  2   E088-19-14.4 0881914.4  88.3208056

B1 and down:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""),"N", ""), "E", ""), "W", "-"), "S", "-")


C1 and down:

=(TEXT(ABS(B1) + 0.5, "0\:00\:00.00")* 24) * SIGN(B1)