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!
With a formula you could do SUBSTITUTE(SUBSTITUTE({Field}, " ", ", "), "Clients: ", "")
Nope, that didn’t work. I get this as the result:
Clients: , Bob, Jenkins, , Sarah, HunterIs 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.
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 HunterBecause 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?
Yes, ive checked the spaces
The formula works for me.
All I can say is double checking the number of spaces included in the formula.
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))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
So far none of the options have worked, and to answer the last question the names are variable and could be up to 6 people.
Can you be more specific when you say that “none of the options have worked”? Have they worked for some records but not for others? What exactly is the output from these formulas using your actual data? We can’t see what you see, so we don’t know what else to suggest unless you give us more detail.
That’s going to be your biggest problem. Both of the solutions provides so far will only work with two names, and were designed that way because the initial sample you provided only contained two names. To parse an arbitrary number of names up to six will require a much more complex solution. I’ve got some ideas on how to approach it, but having more detail from your end would help to focus our efforts in the right direction.
Hi, hope I’m not being rude by tagging into this thread ( first timer ). If so, I apologise in advance.
I have a similar issue with trying to remove the “.jpeg” filename from a bulk import of images I did into a new gallery. I uploaded 1,000 logos for a marketplace site I’m working on and instead of the company name in the Name Field I now have “company name.jpeg”.
!Airtable Issue|700x175](upload://5xD6KA0LRXOypa0arqO130uUDzb.jpeg)
Is there an easy way to remove the .jpeg from all 1,000 cells ?
Thanks in advance.
Your task seems a lot simpler. You may need only this: SUBSTITUTE({Name}, '.jpg', ''), which should replace any instance of ‘.jpg’ with nothing.
@Damon_Talbot Checking in to see if you’re able to provide the further insight that I asked for following your last response (above).
I never could get it to work and just moved on with a work around in my process.