Concatenation Discrepancy

[Carlos_Hurtado]

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!

[Mohamed_Swella1]

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

[kuovonne]

Carlos_Hurtado:

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)

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]

@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??

[kuovonne]

Carlos_Hurtado:

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

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

[Justin_Barrett]

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

[Carlos_Hurtado]

@Justin_Barrett ,

Absolutely brilliant! This is EXACTLY what I needed.

This community never seize’s to amaze me!

Stay safe!

[Carlos_Hurtado]

One last question.

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

[Justin_Barrett]

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.

[Carlos_Hurtado]

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