Lat Long concatenation

AppSheet AppSheet Q&A
1

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

2

@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),".","")
))
)
3

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

4

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

5

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().

1 Like
6

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.

1 Like
7

@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.

8

I understand! I’ll take a look.

1 Like
9

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), ".", "")
  )
)
2 Likes
10

Thanks @Steve, highly appreciated! I believe

", ".

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

(Lat, Long)
1 Like
11

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.

12

Fixed.

1 Like
13

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

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

Have you tried:

LATLONG([LATITUDE], [LONGITUDE])
15

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.

16

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

17

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

18

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

19

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.

20

@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.