Does anybody know of a quick and simple way to convert 19°02’00.2"N 72°50’24.0"E into Latitude Longitude coordinates which Glide understands?
1 Like
How are you getting the lat long? As a single string, or can you get it in separate fields for each part of it? (Degrees, minutes seconds)
Single string. I already have a database with 300 rows which needs to be converted
You might have to do a series of splits in the string in google sheets to separate out the degrees, minutes, and seconds. Directional indicators could be stripped out all together. Then check out this page on how to do the math to convert it.
Basically you get the decimal equivalent of the minutes and seconds, then add them to the degrees so you have a single number.
1 Like
Probably I’ll jump in to see what can be done later today.
1 Like
I went a little different approach, which is a little more forgiving on character length for each portion, rounds to 8 decimals, and joins it into one result. Maybe @ThinhDinh has an idea how to streamline without the repeated index/split/substitute.
=TEXT(INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 1) +
INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 2)/60 +
INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 3)/3600, "0.00000000")
& ", " &
TEXT(INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 5) +
INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 6)/60 +
INDEX(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A, " ", "*"), "°", "*"), "’", "*"), """", "*"), "*"), 1, 7)/3600, "0.00000000")
The substitutes for minutes ' and seconds " may potentially be an issue since they are different characters from ’ and ”, so maybe some extra substitutes to account for the different characters.
3 Likes
I think your solution works best here, that’s clean and I can’t think of a way to make it shorter for now.
2 Likes
I tried to get it all in one column. The only thing I would maybe do different is move the substitutes into a separate column and then reference that column in the split formulas. Also I would maybe change the substitute value to something other than * since I think people tend to use * for the degrees symbol when typing lat/long.
1 Like
Just put the spade like what we did in the very long split formula haha. No one in their right mind will use that.
1 Like
Ha! true.
1 Like
Just for a bit of fun, here’s a solution using regular expressions.
Formulas:
Latitude: =REGEXEXTRACT(A2,"^\d+")+REGEXEXTRACT(A2,"^\d+.(\d+)")/60+REGEXEXTRACT(A2,"^\d+.\d+.([\d\.]+)")/3600
Longitude: =REGEXEXTRACT(A2,"^.*\s(\d+)")+REGEXEXTRACT(A2,"^.*\s\d+.(\d+)")/60+REGEXEXTRACT(A2,"^.*\s\d+.\d+.([\d\.]+)")/3600
NB: I would definitely not recommend using this method, just posting this to demonstrate TIMTOWTDI 
3 Likes
I always admire people who can use regexextract, to be honest 
It just occurred to me that all 3 solutions you’ve been given will ONLY work for northern latitudes.
Here’s an update to my regex solution (for the latitude) that will work for both sides of the equator…
=IF(REGEXEXTRACT(A2,"^.*(.)\s")="N",REGEXEXTRACT(A2,"^\d+")+REGEXEXTRACT(A2,"^\d+.(\d+)")/60+REGEXEXTRACT(A2,"^\d+.\d+.([\d\.]+)")/3600,-REGEXEXTRACT(A2,"^\d+")-REGEXEXTRACT(A2,"^\d+.(\d+)")/60-REGEXEXTRACT(A2,"^\d+.\d+.([\d\.]+)")/3600)
But again, please don’t use this as it’s awful 
Challenge to @Jeff_Hager & @ThinhDinh - fix yours so they work for northern and southern latitudes 
2 Likes
Thanks guys. This is super useful! And you are all legends!!
1 Like
Challenge accepted. Made a few changes.
- I reworked it into 2 columns: one with all the substitutes and one for the conversion to decimal.
- I changed the split delimiter to a spade to prevent issues.
- Added * as an option for the degrees symbol
- Added both versions of single and double quotes to account for differences in character sets when typing in the minutes and seconds on a Lat/long.
- Converted to an ArrayFormula, but realized INDEX is not compatible, so switched to use QUERY. (It’s slower, which I don’t like and I’m open to improvements)
- Most importantly, accounts for North/South East/West +/-
Here’s the sheet
2 Likes
oops, I overlooked that! 
2 Likes
Okay, so I had to fix mine as well.
- Now deals correctly with NSEW +/-
- Converted to use
ARRAYFORMULA
Google Sheet here
4 Likes