Lat Long concatenation

[Aparicio_Pineyr]

Hi there!
I had this Lat and Long columns values, I try to concatenate them so I can Map them from a LatLong column but it seems that is not working, any comments on how can I transform this data so I can Pin Map them as Latlong?

[LATITUDE] -26.406.342.000.000.000
[LONGITUDE] 153.083.711.000.000.000

[LeventK]

@Aparicio_Pineyrua can you try with this?

CONCATENATE(
IFS(
AND(LEFT([LATITUDE],1)="-",FIND(".",[LATITUTE])=4),
SUBSTITUTE(RIGHT(LEFT([LATITUDE],11),4),".",""),
AND(LEFT([LATITUDE],1)="-",FIND(".",[LATITUTE])=5),
SUBSTITUTE(RIGHT(LEFT([LATITUDE],12),4),".",""),
TRUE,
IFS(
FIND(".",[LATITUDE])=3,
SUBSTITUTE(RIGHT(LEFT([LATITUDE],10),4),".",""),
FIND(".",[LATITUDE])=4,
SUBSTITUTE(RIGHT(LEFT([LATITUDE],11),4),".","")
)),
", ",
IFS(
AND(LEFT([LONGITUDE],1)="-",FIND(".",[LONGITUDE])=4),
SUBSTITUTE(RIGHT(LEFT([LONGITUDE],11),4),".",""),
AND(LEFT([LONGITUDE],1)="-",FIND(".",[LONGITUDE])=5),
SUBSTITUTE(RIGHT(LEFT([LONGITUDE],12),4),".",""),
TRUE,
IFS(
FIND(".",[LONGITUDE])=3,
SUBSTITUTE(RIGHT(LEFT([LONGITUDE],10),4),".",""),
FIND(".",[LONGITUDE])=4,
SUBSTITUTE(RIGHT(LEFT([LONGITUDE],11),4),".","")
))
)

[Steve]

Every occurrence of LEFT() appears to be missing a length.

[LeventK]

Thanks for the input @Steve. Can you explain why?

[Steve]

Shouldn’t LEFT([LATITUDE]) be LEFT([LATITUDE], 1)?

SUBSTITUTE(RIGHT(LEFT([LONGITUDE]),11),4) has no length for LEFT(), a length of 11 for RIGHT(), and 4 goes to SUBSTITUTE().

[LeventK]

Good catch @Steve, TY! From the mobile I might have lost the total command. Corrected the 1st part, and now check for the 2nd. Appreciated.

[LeventK]

@Steve
I believe I have corrected the expression. May I kindly request from you to take a look at it now? From a 5.5" screen it’s a bit hard you know. Thanks.

[Steve]

I understand! I’ll take a look.

[Steve]

With some corrections and reformatted for clarity:

CONCATENATE(
  IFS(
    AND(
      (LEFT([LATITUDE], 1) = "-"),
      (FIND(".", [LATITUDE]) = 4)
    ),
      SUBSTITUTE(RIGHT(LEFT([LATITUDE], 11), 4), ".", ""),
    AND(
      (LEFT([LATITUDE], 1) = "-"),
      (FIND(".", [LATITUDE]) = 5)
    ),
      SUBSTITUTE(RIGHT(LEFT([LATITUDE], 12), 4), ".", ""),
    (FIND(".", [LATITUDE]) = 3),
      SUBSTITUTE(RIGHT(LEFT([LATITUDE], 10), 4), ".", ""),
    (FIND(".", [LATITUDE]) = 4),
      SUBSTITUTE(RIGHT(LEFT([LATITUDE], 11), 4), ".", "")
  ),
  ", ",
  IFS(
    AND(
      (LEFT([LONGITUDE], 1) = "-"),
      (FIND(".", [LONGITUDE]) = 4)
    ),
      SUBSTITUTE(RIGHT(LEFT([LONGITUDE], 11), 4), ".", ""),
    AND(
      (LEFT([LONGITUDE], 1) = "-"),
      (FIND(".", [LONGITUDE]) = 5)
    ),
      SUBSTITUTE(RIGHT(LEFT([LONGITUDE], 12), 4), ".", ""),
    (FIND(".", [LONGITUDE]) = 3),
      SUBSTITUTE(RIGHT(LEFT([LONGITUDE], 10), 4), ".", ""),
    (FIND(".", [LONGITUDE]) = 4),
      SUBSTITUTE(RIGHT(LEFT([LONGITUDE], 11), 4), ".", "")
  )
)

[LeventK]

Thanks @Steve, highly appreciated! I believe

", ".

is missing between 2 IFS statements inside the CONCATENATE as the syntax/format shall be:

(Lat, Long)

[Steve]

Fixed.

[Aparicio_Pineyr]

Thanks @LeventK and @Steve but for some reason is not working.
I try changing the columns Lat and Long for text and numeric and is not showing the pins in the Map form the LatLong column with this formula.

[LeventK]

@Aparicio_Pineyrua
I believe you need to wrap all expressions of SUBSTITUTE(…) with DECIMAL()

DECIMAL(SUBSTITUTE(......))

[Steve]

Have you tried:

LATLONG([LATITUDE], [LONGITUDE])

help.appsheet.com

LATLONG()

LatLong from components

[Aparicio_Pineyr]

Yes I try allready and is not working.
I aslo change the format of the columns in both side, don´t understand why is not working.

[Steve]

The LATITUDE and LONGITUDE columns should be of type Decimal, not Number or Text.

[Steve]

In the lat and long you provided, where does the decimal point occur?

[Aparicio_Pineyr]

I try decimal all ready.
LAT -27.830.347.496.399.400,00
LONG 152.088.817.968.540.000,00

[Steve]

27.830.347.496.399.400,00 and 152.088.817.968.540.000,00 are not valid, I don’t believe. Try 27,8303474963994 and 152,08881796854 instead.

[Steve]

en.wikipedia.org

Decimal degrees

Decimal degrees (DD) express latitude and longitude geographic coordinates as decimal fractions and are used in many geographic information systems (GIS), web mapping applications such as OpenStreetMap, and GPS devices. Decimal degrees are an alternative to using degrees, minutes, and seconds (DMS). As with latitude and longitude, the values are bounded by ±90° and ±180° respectively. Positive latitudes are north of the equator, negative latitudes are south of the equator. Positive longitudes a...

[LeventK]

@Aparicio_Pineyrua
If these are correct values than you need to divide them by 10^15 as they are totally out of range with these values.

[LeventK]

@Aparicio_Pineyrua
I would suggest trying with this:

LATLONG(
DECIMAL([LATITUDE]/1000000000000000), 
DECIMAL([LONGITUDE]/1000000000000000)
)

OR simply

LATLONG(
[LATITUDE]/1000000000000000.00,
[LONGITUDE]/1000000000000000.00
)

[Aparicio_Pineyr]

Will try thanks!

[Aparicio_Pineyr]

It´s working but is not been accurate in the map.

[LeventK]

@Aparicio_Pineyrua
It might be because of the decimal place approximation. Try with this and verify the result:

LATLONG(
[LATITUDE]/1000000000000000.00000000,
[LONGITUDE]/1000000000000000.00000000
)

With 2 significant digits, your coordinates will look like:
LATLONG(-96.21, 153.85)
In above expression, I have raised the significant digits to 8, so they will look like:
LATLONG(27.83034749, 152.08881796)

Also remember this; I’m not aware how and from where/what kind of resource you get those LAT and LONG values, but the GPS coordinate capture differs from device to device. Most accurate ones are always the GPS devices. Mobile phones (eventhough they have a small GPS module) uses the triangulation method to calculate the location where the base data is captured from the GSM network stations. Only military grade GPS devices have the proximity up to 50cm.