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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.