Left Right Function Help

[Southernwave]

I have a small issue with the LEFT and RIGHT Function which has me scratching my head....

I am using long/latitude information which comes in in the following format.....

-33.8688197 , 151.2092955
37.7749295 , -122.4194155

But I want to end up with something like ..

{lat: 37.7749295, Lng: -122.4194155},//San Francisco

I currently use the following

=IF(H5<>"","{lat: "& LEFT(H5,(FIND(",",H5,1)-1))&", lng: "& RIGHT(H5,(FIND(",",H5)+1))&"}," & " //" & (D5),"")

And mostly its all good....but for th first of the above I get a stray "," after "lng:" in the result

{lat: -33.8688197 , lng: , 151.2092955},//sydney

It has me "confused"

What am I doing wrong ?

Andrew

[offthelip]

You are counting the characters from the left hand end but the selecting the characters from the right hand end: so try this:

[vba]=IF(H5<>"","{lat: "& LEFT(H5,(FIND(",",H5,1)-1))&", lng: "& RIGHT(H5,(LEN(H5)-FIND(",",H5)))&"}," & " //" & (D5),"")[/vba]

[Southernwave]

Hi and thank you for the Fastttt reply

Your answer almost fixed the issues... but on some of the results a ",," instead of single "'"



To fix that I changed your suggestion slightly to

 =IF(H5<>"","{lat: "& LEFT(H5,(LEN(H5)-FIND(",",H5,1)-2))&", lng: "& RIGHT(H5,(LEN(H5)-FIND(",",H5)))&"}," & " //" & (D5),"")

And now all working ... thank YOU for the help

A

[offthelip]

Hi my origianl post had an error in it which I corrected a short whoile after I posted it, unfortunately you picked up the code with the errror in it:
my original uses LEN for both left and right which is wrong:
=IF(H5<>"","{lat: "& LEFT(H5,(len(h5)-FIND(",",H5,1)-1))&", lng: "& RIGHT(H5,(LEN(H5)-FIND(",",H5)))&"}," & " //" & (D5),"")

the corrected code is


JoinedMay 2016Posts116Location

You are counting the characters from the left hand end but the selecting the characters from the right hand end: so try this:

=IF(H5<>"","{lat: "& LEFT(H5,(FIND(",",H5,1)-1))&", lng: "& RIGHT(H5,(LEN(H5)-FIND(",",H5)))&"}," & " //" & (D5),"")

=IF(H5<>"","{lat: "& LEFT(H5,(FIND(",",H5,1)-1))&", lng: "& RIGHT(H5,(LEN(H5)-FIND(",",H5)))&"}," & " //" & (D5),"")

Your code is still not right even if it does work, :
when using LEFT your need to count characters from the left i:e FIND on it's own
when using RIGHT you need to count characters from the right i:e use LEN() - FIND()

[Southernwave]

Hi ,, even better... fixed a issue that came up this afternoon... thanks for chasing me

A