Substitute characters in a string with another set of characters

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.

image

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”

image

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.

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.

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

2 Likes

Works. You’re a star!

1 Like

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

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.

1 Like

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.

1 Like

Hi Kuovonne,

First of all, I have to give you a shout out for the Prefill Forms app. Brilliant and mega useful.
Scott’s Array formula is good enough. I placed hyphens and the demarcation is fine now, since I don’t see a way for Prefill Forms to render these hyphens as spaces (%20) in the target field.

You can see it in this example (click on the Inquire link).

https://airtable.com/shr8fHjdOUTvgVgrJ/tblgtXCOAYwhJfLLP

The form allows a lead to inquire about the services of a workforce member available for hire. The form submits to an Inquiries table, which is, at the moment, a primitive CRM.

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

3 Likes

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.

1 Like

I thought about that, but I don’t like having accounts that are only used for testing and nothing more. I’m probably in the minority there, but that’s just how my brain prefers it.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.