Substitute characters in a string with another set of characters

[Milos_Milosavlj]

Hello,

I am trying to substitute all the occurrences of ", " (comma, space) in a Lookup field with “%20” and place that into the formula field.

[ScottWorld]

In your formula, you will need to turn your lookup field into a string, by adding an empty string after the field name:

{Inquiry: Skills List Internal} & ""

So your whole formula would look like this:

SUBSTITUTE( {Inquiry: Skills List Internal} & "", ",", "%20")

[Milos_Milosavlj]

ScottWorld:

SUBSTITUTE( {Inquiry: Skills List Internal} & “”, “,”, “%20”)

Hm, @ScottWorld it removed the commas, but did not substitute with “%20”

[ScottWorld]

Hmmm… not sure… the same formula works for me on my end.

[Milos_Milosavlj]

@ScottWorld , is the source field a Lookup field in your case as well?

[ScottWorld]

Yes. Same setup as you.

[Milos_Milosavlj]

ScottWorld:

SUBSTITUTE( {Inquiry: Skills List Internal} & “”, “,”, “%20”)

The formula works when I apply it on a single line text field, but not a lookup field or formula field (I just copied the lookup string into a formula field by inserting it).
I’ll keep trying.

[ScottWorld · Accepted solution]

Oh, interesting… actually, my setup WASN’T identical to yours. My lookup field was actually looking up a collaborator field, and it worked for that type of field. When I switched it to a different field type, it failed.

Try this instead for your formula, and see if this works:

ARRAYJOIN( {Inquiry: Skills List Internal}, "%20" )

[Milos_Milosavlj]

Works. You’re a star!

[ScottWorld]

Fantastic! I probably shouldn’t have gone down the path of that first formula at all! :winking_face: