Help

Re: Concatenation Discrepancy

1522 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Carlos_Hurtado
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello community!

I am in need of your help.
I am concatenating text in various cells. When all cells in (Fields 1-6 is filled the result is perfect.
However when data is missing from some of the Fields it generates a double underscore.
How could I avoid this nuisance ?

Formula being used:
CONCATENATE({Field 1}, “", {Field 2}, "”, {Field 3}, “", {Field 4}, "”,{Field 5}, “_”, {Field 6}, Extension)

Thanks you so much and stay safe!

9 Replies 9

Hi @Carlos_Hurtado,

This seems like a nested If job. The first solution that comes to mind is to have if statements that does the concatenation while dropping the empty cells.

But this will get messy if there is more than 1 empty cell at a time.

BR,
Mo

It looks like this formula should produce only a single underscore between {Field 5} and {Field 6}.
So, it is strange that it is producing a double underscore. Do you have underscores in any of the field values?

It also looks like the formula might not be displaying correctly. Do you have asterisks * inside the quote marks? If you want the formula to show on these forums exactly as you type it, format it as a markdown code block.

Here is a concatenation formula that will concatenate seven fields, separating items with a comma and omitting commas where there are empty cells. (Note: it will not work correctly for number fields that contain zero values. That would be a much longer formula.) Perhaps you can adapt this formula for whatever symbol you want to use between items.

{field1} & IF(AND({field1}, OR({field2}, {field3}, {field4}, {field5}, {field6}, {field7})), ", ")
& {field2} & IF(AND({field2}, OR({field3}, {field4}, {field5}, {field6}, {field7})), ", ")
& {field3} & IF(AND({field3}, OR({field4}, {field5}, {field6}, {field7})), ", ")
& {field4} & IF(AND({field4}, OR({field5}, {field6}, {field7})), ", ")
& {field5} & IF(AND({field5}, OR({field6}, {field7})), ", ")
& {field6} & IF(AND({field6}, {field7}), ", ")
& {field7}

Carlos_Hurtado
5 - Automation Enthusiast
5 - Automation Enthusiast

@kuovonne Works great! But it seems it isn’t possible to replace the ", " with a "_ ".

Example:
{Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7})), “_ “)

& {Field 2} & IF(AND({Field 2}, OR({Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7})), “_ “)

& {Field 3} & IF(AND({Field 3}, OR({Field 4}, {Field 5}, {Field 6}, {Field 7})), “_ “)

& {Field 4} & IF(AND({Field 4}, OR({Field 5}, {Field 6}, {Field 7})), “_ “)

& {Field 5} & IF(AND({Field 5}, OR({Field 6}, {Field 7})), “_ “)

& {Field 6} & IF(AND({Field 6}, {Field 7}), “_ “)

& {Field 7}

Any thoughts??

You should be able to replace the comma with an underscore.
Make sure that you have straight quotes " instead of curly quotes “”.

If {Field 1} is a given, you can get away with a much simpler formula:

{Field 1}
& IF({Field 2}, "_" & {Field 2})
& IF({Field 3}, "_" & {Field 3})
& IF({Field 4}, "_" & {Field 4})
& IF({Field 5}, "_" & {Field 5})
& IF({Field 6}, "_" & {Field 6})
& IF({Field 7}, "_" & {Field 7})

Screen Shot 2020-04-04 at 10.08.21 PM

Carlos_Hurtado
5 - Automation Enthusiast
5 - Automation Enthusiast

@Justin_Barrett ,

Absolutely brilliant! This is EXACTLY what I needed.

This community never seize’s to amaze me!

Stay safe!

Carlos_Hurtado
5 - Automation Enthusiast
5 - Automation Enthusiast

One last question.

Is there a a way to “trigger” this formula IF and Only IF a certain word exist in another column?

Unfortunately formulas can’t be “triggered,” at least not in the sense of having it only run when you want it to run. Whenever any field used by a formula changes, the formula will recalculate, but only when it calculates can it check the data and operate on it. In other words, the trigger isn’t the specific data itself. The trigger is the fact that the field data was changed.

I’m guessing that you want a formula to run at a certain point in your workflow, and maintain that formula result permanently. To accomplish that, you will need to either use the Scripting block (a Pro workspace feature), enlist the help of an integration tool like Zapier or Integromat, or write custom API code, any of which will do the calculation separately and populate a regular data field (i.e. not a formula field) with your desired result.

@Justin_Barrett thank you very much for the clarity. I will definitely looking in to Zappier and Integromat.