Substitute characters in a string with another set of characters

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")

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

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

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

Yes. Same setup as you.

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" )

Works. You’re a star!

The problem with the original formula was that when you turn a lookup field into a string with & "", the resulting string usually does not have commas in it. Thus, it seemed like the original formula was removing commas, but not encoding the spaces, because there were no commas to begin with. The spaces were not encoded because there were no commas and spaces to for the SUBSTITUTE to find.

This issue with lookups seeming to have commas results from the fact that lookups are arrays, and when they are displayed as arrays, they are formatted with commas, but when they are converted to strings, there are no commas. Super confusing? Yes. Likely to change? No. Just use rollups instead whenever possible.

The problem with the original formula was that when you turn a lookup field into a string with & "", the resulting string usually does not have commas in it. Thus, it seemed like the original formula was removing commas, but not encoding the spaces, because there were no commas to begin with. The spaces were not encoded because there were no commas and spaces to for the SUBSTITUTE to find.

This issue with lookups seeming to have commas results from the fact that lookups are arrays, and when they are displayed as arrays, they are formatted with commas, but when they are converted to strings, there are no commas. Super confusing? Yes. Likely to change? No. Just use rollups instead whenever possible.

Here’s what’s so bizarre & inconsistent about Airtable’s handling of this:

When you convert a lookup field that looks up a Collaborator field into a string, Airtable retains the commas & you can substitute the commas. :man_shrugging: :man_facepalming:

I haven’t tested this with all field types, but I’m assuming that this weird inconsistency might possibly exist with other field types as well.

@Justin_Barrett might have already documented this strangeness in his Field Reference Database, although I can’t find the link to his thread about his base at the moment.

Yep, seems like rollups are the safest way to go here.

p.s. For any new users who are creating rollup fields for the first time, there is a small bug with rollup fields too… you can’t have any fields in your base called “Values” before creating a rollup field in that table.

Here’s the URL for the base: http://bit.ly/airtablefieldreference

And here’s what that URL expands into:

I wasn’t able to test multiple collaborators in lookup fields as thoroughly as I’d hoped because I never work in bases where multiple people are collaborating, and I haven’t found a way to “fake” multiple collaborators to run a test (though I’ve never really looked that hard).

One way to have test having multiple collaborators is to create a new Airtable account for yourself using a different email address. Then, add your other account as.a read-only collaborator so that you won’t be charged.