Need help removing a word, and also extra space between other words

[Damon_Talbot]

I have a field that is bringing in names using the web clipper block. The problem I am having is the names from the site come in with additional header, and extra space between them. Is there a way to clean this up in a formula or in the web clipper on import? Here is the example:

Clients:  Bob Jenkins     Sarah Hunter

I need it to look like this:

Bob Hunter, Sarah Hunter

Thanks as always!

[Kamille_Parks]

With a formula you could do SUBSTITUTE(SUBSTITUTE({Field}, " ", ", "), "Clients: ", "")

[Damon_Talbot]

Nope, that didn’t work. I get this as the result:

Clients: , Bob, Jenkins,   , Sarah, Hunter

[Kamille_Parks]

Is Web Clipper always adding 2 spaces after “Clients:” and 5 spaces between each client name? If so then try:

TRIM(SUBSTITUTE(SUBSTITUTE({Field}, "Clients:", ""), **" "**, ", "))

The part with double asterisks around it, make sure there are 5 spaces between the quotes, don’t actually include asterisks in your formula.

[Damon_Talbot]

Kamille_Parks:

TRIM(SUBSTITUTE(SUBSTITUTE({Field}, “Clients:”, “”), " ", ", "))

Same issue I’ve been having… for some reason it does not remove the space between names or add the comma. I’ve been trying combinations of trim, substitute, replace, and banging my head.

Bob Jenkins    Sarah Hunter

[Kamille_Parks]

Because this forum truncates double spaces I can’t really tell if its the case, but when you’re putting the formula in Airtable are you including the right number of spaces?

[Damon_Talbot]

Yes, ive checked the spaces

[Kamille_Parks]

The formula works for me.

All I can say is double checking the number of spaces included in the formula.

[Justin_Barrett]

Perhaps the number of spaces between names isn’t that predictable. Here’s an option that’s a little more loosely defined. It’s split into two formulas because the second one uses the first one twice.

The {Step 1} formula removes the “Clients:” header, replaces any instance of two consecutive spaces with a block of 30 spaces, and trims the result. We’re left with just the names and a big gap of spaces that’s guaranteed to be at least 30 spaces wide (most likely bigger).

TRIM(SUBSTITUTE(SUBSTITUTE(Original, "Clients:", ""), "  ", REPT(" ", 30)))

(BTW: depending on how you add code to your post, you can get the forum parser to leave larger space blocks alone. I used the 4-space indentation method above. If you wrap your code in matching graves-triplets, large blocks of spaces are condensed. Learned something new today!)

Here’s what that {Step 1} output looks like with the field value expanded:

The second formula takes that result and extracts the names by removing 30-character chunks from the beginning and end, and adding the comma in the middle. Because we’re assuming that no name will be 30 characters long, each 30-character extraction returns a name and a portion of the at-least-30-spaces-wide block between names, but none of the other name.

TRIM(LEFT({Step 1}, 30)) & ", " & TRIM(RIGHT({Step 1}, 30))

If you want all of this in a single formula, here you go:

TRIM(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(Original, "Clients:", ""), "  ", REPT(" ", 30))), 30)) & ", " & TRIM(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(Original, "Clients:", ""), "  ", REPT(" ", 30))), 30))

[Kamille_Parks]

Will clients always be two names? Justin’s formula solves the unpredictability of variable numbers of spaces, but you may run into issue if you have 3 or more clients