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

Manipulating strings

Alex Hartley Jul 25, 2017 2:56 PM

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.

1. Re: Manipulating strings

Galen Busch Jul 25, 2017 3:00 PM (in response to Alex Hartley)

Hi Alex,

Simply put, this is easier with find and replace in Excel (Control+H) or a replace function in SQL. Are either of those options?
Like Show 0 Likes(0)

Actions
2. Re: Manipulating strings

Stoyko Kostov Jul 25, 2017 3:04 PM (in response to Alex Hartley)

Have you tried REPLACE([ADDRESS], " ", "+")? This would simply replace all spaces with pluses. Please let me know if this is not quite what you are trying to achieve - I can try to improve it using different string functions.
Like Show 0 Likes(0)

Actions
3. Re: Manipulating strings

Alex Hartley Jul 25, 2017 3:04 PM (in response to Galen Busch)

yes, you are right. I have done it in excel before. the problem is that I have a dashboard that displays the address and I want users to click on that address and open the search engine in salesforce to look for opportunities in that address. the search engine uses a specific link and the only thing that changes is the end, which is the "6251+main+st" portion. makes sense?
Like Show 0 Likes(0)

Actions
4. Re: Manipulating strings

Alex Hartley Jul 25, 2017 3:05 PM (in response to Stoyko Kostov)

oh man!!! didn't think of using REPLACE. let me try it.
Like Show 0 Likes(0)

Actions
5. Re: Manipulating strings

Alex Hartley Jul 25, 2017 3:06 PM (in response to Stoyko Kostov)

Stoyko, it worked. thank you!!
Like Show 0 Likes(0)

Actions