# Geocoding help needed

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

If your format strictly follows this then the arrayformulas here will work.

3 Likes

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

@Manan_Mehta

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`