# Geocoding help needed **URL:** \ **Category:** Ask for Help\ **Created:** [November 17, 2020, 10:45pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586 "2020-11-17T22:45:47Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\ **Post date:** [November 17, 2020, 10:45pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/1 "2020-11-17T22:45:47Z")
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? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 17, 2020, 10:58pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/2 "2020-11-17T22:58:46Z")
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) ---
**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\ **Post date:** [November 17, 2020, 11:01pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/3 "2020-11-17T23:01:29Z")
Single string. I already have a database with 300 rows which needs to be converted ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 17, 2020, 11:05pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/4 "2020-11-17T23:05:32Z")
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. > **[How to Convert Latitude Degrees to Decimal](https://sciencing.com/convert-latitude-degrees-decimal-7486138.html)** > > Latitude measurements are imaginary lines that run around the earth, parallel to the equator. Degrees of latitude are the opposite of degrees of longitude, which are imaginary lines that run around the earth perpendicular to the equator. Together... ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [November 17, 2020, 11:14pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/5 "2020-11-17T23:14:36Z")
Probably I’ll jump in to see what can be done later today. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [November 18, 2020, 12:01am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/6 "2020-11-18T00:01:34Z")
> [@Manan\_Mehta](#): > > 19°02’00.2"N 72°50’24.0"E If your format strictly follows this then the arrayformulas here will work. > **[liquidacion dac fabiola](https://docs.google.com/spreadsheets/d/1JzBrp4R1zK9CMfmTzWWcPPep9-ZCl3OXxgWu27NWCy8/edit#gid=1948008531)** > > App: Comments > > ID,Topic,Username,Email,Time,Comment > 2m62FCts4yTZ4ziCBZqZ,We hope you will be able to join us in our app!,Thinh Dinh,ariesarsenal@gmail.com,2021-01-16T01:11:38.833Z,Test comment ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/a/3a3166b959e65d6ff848920d40ef02165dc8b221.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/2/f206e8da03c336290646bebcf3bc9f5856201256.jpeg) ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 18, 2020, 12:06am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/7 "2020-11-18T00:06:28Z")
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. ```auto =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. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [November 18, 2020, 12:32am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/8 "2020-11-18T00:32:50Z")
I think your solution works best here, that’s clean and I can’t think of a way to make it shorter for now. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 18, 2020, 12:56am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/9 "2020-11-18T00:56:56Z")
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. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [November 18, 2020, 12:58am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/10 "2020-11-18T00:58:09Z")
Just put the spade like what we did in the very long split formula haha. No one in their right mind will use that. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 18, 2020, 1:12am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/11 "2020-11-18T01:12:11Z")
Ha! true. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [November 18, 2020, 1:57am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/12 "2020-11-18T01:57:41Z")
Just for a bit of fun, here’s a solution using regular expressions. ![Screen Shot 2020-11-18 at 9.49.35 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/6/a66f90f875eb581afeb9e19ae15d9892e5b7c3b7.png) 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 😆 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [November 18, 2020, 1:58am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/13 "2020-11-18T01:58:48Z")
I always admire people who can use regexextract, to be honest 😅 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [November 18, 2020, 7:17am UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/14 "2020-11-18T07:17:16Z")
@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… ```auto =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) ``` ![Screen Shot 2020-11-18 at 3.37.13 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/e/6efa34e35d12f9c370f5b9f5fbd47646550bcb09.png) 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 😛 ---
**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\ **Post date:** [November 18, 2020, 1:03pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/15 "2020-11-18T13:03:33Z")
Thanks guys. This is super useful! And you are all legends!! ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [November 18, 2020, 2:32pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/16 "2020-11-18T14:32:54Z")
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 +/- ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/5/c5d0874846c69242716176c90c0e3ce8e03c3bb0.png) Here’s the sheet > **[LatLong to Degrees](https://docs.google.com/spreadsheets/d/1abTl3XN_j0NDlmeXlraEaLsLYjE2HcM-n32yMz7-fUQ/edit?usp=sharing)** > > Sheet1 > > LatLong,Substitute,Decimal > 19°02’00.2"N 72°50’24.0"E,19♠02♠00.2♠N♠72♠50♠24.0♠E,19.03338889, 72.84000000,19.03338889, 72.84000000 > 19°02’00.2"s 72°50’24.0"E,19♠02♠00.2♠s♠72♠50♠24.0♠E,-19.03338889, 72.84000000 > 19°02’00.2"N... ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [November 18, 2020, 2:43pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/17 "2020-11-18T14:43:12Z")
> [@Jeff\_Hager](#): > > Most importantly, accounts for North/South East/West +/- oops, I overlooked that! 🤪 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [November 18, 2020, 3:23pm UTC](https://community.glideapps.com/t/geocoding-help-needed/18586/18 "2020-11-18T15:23:08Z")
Okay, so I had to fix mine as well. - Now deals correctly with NSEW +/- - Converted to use `ARRAYFORMULA` ![Screen Shot 2020-11-18 at 11.19.41 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/2/a2be6071c2410fbb0d05e57c5baf17b1c4afd00d.png) Google Sheet [here](https://docs.google.com/spreadsheets/d/1EosXp2FiXozAI4rRQnWiHuVw7Dbh7DqEPvyrJJ70_Us/edit?usp=sharing)