PDA

View Full Version : [SOLVED] Left Right Function Help



Southernwave
12-28-2016, 03:32 PM
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" :wot

What am I doing wrong ?

Andrew

offthelip
12-28-2016, 04:31 PM
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),"")

Southernwave
12-28-2016, 08:24 PM
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
12-29-2016, 02:37 AM
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 2016Posts116Locationhttp://www.vbaexpress.com/forum/images/flags/United%20Kingdom%203D.gif http://www.vbaexpress.com/forum/images/flags/states/%203D.gif


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
12-29-2016, 05:56 AM
Hi ,, even better... fixed a issue that came up this afternoon... thanks for chasing me

A