5 Replies Latest reply on Jul 25, 2017 3:06 PM by Alex Hartley

    Manipulating strings

    Alex Hartley

      Hello, I am trying to convert these 2 addresses:

       

      6251 main st

      123 s river dr w

       

      into

       

      6251+main+st

      123+s+river+dr+w

       

      and I was using a combination of FIND(), FINDNTH() and MID() functions to do it, and I got it partially to work:

       

      str(LEFT([ADDRESS],FIND([ADDRESS]," ")-1))  +  '+'  + MID([ADDRESS],FIND([ADDRESS]," "),(FINDNTH([ADDRESS]," ",2)-FIND([ADDRESS]," ")))

       

      However, It it is not working when the number of spaces in the address is different.  When looking for the 3rd word in the  long address, the short address returns blank because it only has 2 spaces. Is there any way to correct this? I can't seem to figure out a solution for this one. Thank you in advanced.