help with formula to convert GPS degrees minutes seconds to decimal degrees

[tbruce]

Hi All,

I have a spreadsheet with a cell (body) with longitude and latitude I'm parsing using formulas. What I'm trying to do is parse the longitude and latitude which is in degrees, minutes, seconds and in this specific format and to parse and convert it to decimal degrees. Here is a sampling of the data. I have the parsing formula figured out (with help from the forum), but can't seem to get the conversion part done - hoping to handle both in the same formula.

Any help would be much appreciated.

Body	Latitude	Longitude
Longitude 122 25 49.541W Latitude 037 46 45.304N Altitude 8 meter (from sea level)	037 46 45.304N	122 25 49.541W

This GPS coordinate in degrees minutes seconds converted via decimal degrees is 37.77925111, -122.4304281

Here are the formulas I'm using to parse the latitude and longitude. Hoping someone could help me incorporate both the parsing and conversion into one formula per value:

Latitude =REPLACE(LEFT(A2,FIND("Altitude",A2)-4),1,FIND("Latitude",A2)+9,"")

longitude =REPLACE(LEFT(A2,FIND("Latitude",A2)-4),1,FIND("Longitude",A2)+9,"")

 

[Peter_SSs]

I have the parsing formula figured out (with help from the forum), .....

Here are the formulas I'm using to parse the latitude and longitude.

Are you sure? Neither of those formulas are as suggested in your other thread about this.
They also do not produce the values shown in your table in this thread.

Further, your one sample here lists Longitude, then Latitude then Altitude. The data in the other thread lists Latitude then Longitude then Altitude.
Is there any consistency how the data is presented? If so, what is the actual order of the three components?

 

[Peter_SSs]

Assuming the order is Latitude, Longitude, Altitude like the original thread then try these.

22 03 06.xlsm
	A	B	C
1	Body	Latitude	Longitude
2	Latitude 037 46 45.304N Longitude 122 25 49.541W Altitude 182 meter (above sea level)	37.77925111	-122.4304281
3	Latitude 047 03 45.000S Longitude 002 59 49.541E Altitude 18 meter (above sea level)	-47.0625	2.997094722
Lat Long

Cell Formulas
Range	Formula
B2:B3	B2	=LET(lf,FIND(CHAR(10),A2),La,MID(LEFT(A2,lf-2),10,20),(LEFT(La,3)+MID(La,5,2)/60+MID(La,8,8)/3600)*IF(MID(A2,lf-1,1)="N",1,-1))
C2:C3	C2	=LET(alt,FIND("Altitude",A2),Lo,REPLACE(LEFT(A2,alt-3),1,FIND("Longitude",A2)+9,""),(LEFT(Lo,3)+MID(Lo,5,2)/60+MID(Lo,8,8)/3600)*IF(MID(A2,alt-2,1)="E",1,-1))

 

[tbruce]

Thanks for the response. This didn't work and it's most likely because of what you pointed out. I don't know how to get the actual spreadsheet into this thread to show the formulas like you did and so you have exactly what I have and trying to create it in this forum is proving to not be the best here (new for me). Also this is just a segment of the data and the only data that I'm interested in. There is a date and time above this that I already figured out, and then data below it as well. I will try to get the whole thing in here so you can see exactly what I'm dealing with. Your efforts are very much appreciated.

Part of the problem is the spacing and tabs etc.. that throws off how I parse the data. It seems trying to find it based on position is not always working nor seems to be the best way. I was trying to use the word prior to the value and then the letter at the end N or W to be the end and read everything in between. I couldn't figure that out so I did it by looking at the next line word. I don't think the way I have it is the best but it seems to work with 99% of the data. I have thousands of lines I'm trying to parse.

Also can you provide an explanation of what this is doing? I'm not familiar with the CHAR(10), La, option here. When I use your formulas above, this is what I get for the Latitude and Longitude #VALUE!. And yes you are right, the order of the Latitude and Longitude was incorrect in one of my posts. It's actually Longitude first and Latitude second.

Also when I try to paste the table from Excel into here, it does not retain the spacing tab issue between Longitude and the value. I don't know how to bring that in where the option to paste it in here changes it and removes it. I tried to install the XLBB and it isn't working on my end. Probably user error.

These are the formulas I'm using: Not sure that is the best way but it works.

Date and Time =REPLACE(LEFT(A2,FIND("GMT",A2)-1),1,FIND("for",A2)+3,"")
Latitude =REPLACE(LEFT(A2,FIND("Altitude",A2)-4),1,FIND("Latitude",A2)+9,"")
Longitude =REPLACE(LEFT(A2,FIND("Latitude",A2)-4),1,FIND("Longitude",A2)+10,"")

Body	Start Time	Latitude DD:MM:SS	Latitude	Longitude DD:MM:SS	Longitude
Garmin GPS for 03/05/2022 20:08:37 GMT Confidence Level 90 Longitude 122 25 54.408W Latitude 037 46 47.971N Altitude 72 meter (from sea level)	03/05/2022 20:08:37	037 46 47.971N	#VALUE!	122 25 54.408W	#VALUE!

 

[tbruce]

Ok figured out how to get XL2BB installed and working. Wish I would have done this a long time ago. Always wondered how everyone was doing that. Sorry trying to catch up with the times.

Here is another row example - same formatting just different geo-location information:

Garmin GPS Locations SINGLE row example.xlsx
	A	B	C	D	E	F
1	Body	Start Time	Latitude DD:MM:SS	Latitude	Longitude DD:MM:SS	Longitude
2	Garmin GPS for 03/05/2022 20:38:50 GMT Confidence Level 90 Longitude 122 25 49.541W Latitude 037 46 45.304N Altitude 8 meter (from sea level)	03/05/2022 20:38:50	037 46 45.304N	#VALUE!	122 25 49.541W	#VALUE!
Garmin GPS Locations

Cell Formulas
Range	Formula
B2	B2	=REPLACE(LEFT(A2,FIND("GMT",A2)-2),1,FIND("for",A2)+3,"")
C2	C2	=REPLACE(LEFT(A2,FIND("Altitude",A2)-4),1,FIND("Latitude",A2)+9,"")
D2	D2	=LET(lf,FIND(CHAR(10),A2),La,MID(LEFT(A2,lf-2),10,20),(LEFT(La,3)+MID(La,5,2)/60+MID(La,8,8)/3600)*IF(MID(A2,lf-1,1)="N",1,-1))
E2	E2	=REPLACE(LEFT(A2,FIND("Latitude",A2)-4),1,FIND("Longitude",A2)+10,"")
F2	F2	=LET(alt,FIND("Altitude",A2),Lo,REPLACE(LEFT(A2,alt-3),1,FIND("Longitude",A2)+9,""),(LEFT(Lo,3)+MID(Lo,5,2)/60+MID(Lo,8,8)/3600)*IF(MID(A2,alt-2,1)="E",1,-1))

 

[Peter_SSs]

Firstly, glad that you have XL2BB going - makes things much easier.

Secondly, your existing 'REPLACE' formulas in columns C & E are not returning very good Latitude & Longitude results despite the appearance that they are. Because of the multiple space characters between the word 'Latitude' or 'Longitude' and the actual values, some of those space characters are still attached to the beginning of your results in C & E.

In row 2 below I have suggested wrapping those formulas with a TRIM() function to remove those extra spaces. Cells D2 & F2 then uses those values to convert to decimal.
My understanding though was that you wanted to convert to decimal direct from the original data, so I have suggestions in row 3 for that. See how that goes.

Before any explanations, let's make sure it is working and which version you want to use. You should also have a read about the LET function here and you will see that I am using Laf, La, Lof and Lo as names (variable names) in the LET function.

tbruce.xlsm
	A		C	D	E	F
1	Body		Latitude DD:MM:SS	Latitude	Longitude DD:MM:SS	Longitude
2	Garmin GPS for 03/05/2022 20:38:50 GMT Confidence Level 90 Longitude 122 25 49.541W Latitude 037 46 45.304N Altitude 8 meter (from sea level)		037 46 45.304N	37.77925111	122 25 49.541W	-122.4304281
3	Garmin GPS for 03/05/2022 20:38:50 GMT Confidence Level 90 Longitude 122 25 49.541W Latitude 037 46 45.304N Altitude 8 meter (from sea level)			37.77925111		-122.4304281
Lat Long (2)

Cell Formulas
Range	Formula
C2	C2	=TRIM(REPLACE(LEFT(A2,FIND("Altitude",A2)-4),1,FIND("Latitude",A2)+9,""))
E2	E2	=TRIM(REPLACE(LEFT(A2,FIND("Latitude",A2)-4),1,FIND("Longitude",A2)+10,""))
D2	D2	=LET(Laf,C2,La,LEFT(Laf,LEN(Laf)-1),(LEFT(La,3)+MID(La,5,2)/60+MID(La,8,8)/3600)*IF(RIGHT(Laf,1)="N",1,-1))
D3	D3	=LET(Laf,TRIM(REPLACE(LEFT(A3,FIND("Altitude",A3)-4),1,FIND("Latitude",A3)+9,"")),La,LEFT(Laf,LEN(Laf)-1),(LEFT(La,3)+MID(La,5,2)/60+MID(La,8,8)/3600)*IF(RIGHT(Laf,1)="N",1,-1))
F2	F2	=LET(Lof,E2,Lo,LEFT(Lof,LEN(Lof)-1),(LEFT(Lo,3)+MID(Lo,5,2)/60+MID(Lo,8,8)/3600)*IF(RIGHT(Lof,1)="E",1,-1))
F3	F3	=LET(Lof,TRIM(REPLACE(LEFT(A3,FIND("Latitude",A3)-4),1,FIND("Longitude",A3)+10,"")),Lo,LEFT(Lof,LEN(Lof)-1),(LEFT(Lo,3)+MID(Lo,5,2)/60+MID(Lo,8,8)/3600)*IF(RIGHT(Lof,1)="E",1,-1))

 

[tbruce]

This worked perfectly - I used D3 and F3 to have the parsing and conversion in the same line. Thank you so much for this.

I will review the LET function and the Laf, La, Lof and Lo as names (variable names) in the LET function to try to have a better understanding what you did there.

 

[Peter_SSs]

This worked perfectly - I used D3 and F3 to have the parsing and conversion in the same line. Thank you so much for this.

You're welcome. Thanks for the confirmation.

I will review the LET function and the Laf, La, Lof and Lo as names (variable names) in the LET function to try to have a better understanding what you did there.

So when you are reviewing that function, the names allow you to effectively use variables in the function.
My names/variables are
Laf I used that as short for "Latitude full" meaning the latitude including N or S (eg 037 46 45.304N)
La I used that as short for "Latitude" meaning the actual numerical part (eg 037 46 45.304)
Similar for Longitude