Concatenated Column or Helper Column Help

Sam McDonald

I have a concatenated column which we use as a summary of the row. I need to include another column called External in it but one of the drop down options is Not Applicable. Is there a way I can edit the formula so that it doesn't include any options which return this value? I'm also thinking I can just add a helper column which equals the External column but can exclude any Not Applicable values. I'm stumped on both counts so any help would be greatly appreciated!

=IFERROR(Region@row + "_" + Country@row + "_" + [Category]@row + "_" + "\"" + [Title]@row + "\"" + "_" + Location@row + "_" + [Start Date]@row, "")

Paul Newcome

Have you tried working in the SUBSTITUTE function?

=IFERROR(SUBSTITUTE(Region@row + "_" + Country@row + "_" + [Category]@row + "_" + "\"" + [Title]@row + "\"" + "_" + Location@row + "_" + [Start Date]@row, "N/A", ""), "")




Paul Newcome

Have you tried working in the SUBSTITUTE function?

=IFERROR(SUBSTITUTE(Region@row + "_" + Country@row + "_" + [Category]@row + "_" + "\"" + [Title]@row + "\"" + "_" + Location@row + "_" + [Start Date]@row, "N/A", ""), "")




Mark.poole

@Sam McDonald

To be sure you have another column called External with a drop down option? if I am correct then try this.

=IFERROR(Region@row + "_" + Country@row + "_" + [Category]@row + "_" + "\"" + [Title]@row + "\"" + "_" + Location@row + "_" + [Start Date]@row,+"_"+IF(External@row = "Not Applicable","",External@row),"")

Sam McDonald

Substitute function worked a treat, thanks Paul!

Paul Newcome

Happy to help. 👍️