Help

How to Write Formula that Concatenates with Conditional Commas

Solved
Jump to Solution
1228 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Kristina_Beever
6 - Interface Innovator
6 - Interface Innovator

I am stuck on something that shouldn’t be as hard as I’m making it.
I have several formula fields in my base that are calculating dates based on a # of days entered. I want to put all of these dates together - but I’m having a minor issue. Some projects have 1 date, others have 3 - up to 4 potential dates that need to be concatenated. I have a simple formula written that concatenates them "IF( {FP 1st}, CONCATENATE({FP 1st}, ", ",{Fp 2nd}, ", ",{Fp 3rd}, ", “, {FP 4th}))” - so if the FP 1st isn’t blank, put all of the values together. The issue I’m having is that for the projects that don’t have FP 2nd or higher, I’m still getting the commas. Normally this isn’t a big issue, but this field is going on a shared view for clients. Is there a way to write an IF formula to tackle this? Or set it up differently and try to run an array formula on this field?

1 Solution

Accepted Solutions
Tim_Sullivan
7 - App Architect
7 - App Architect

Just thinking out loud here, but what if you tested it backwards? If the FP 4th field isn’t blank, concatenate them all, if it is blank, look to FP 3rd and do the same thing but only concatenate the FP 1st, 2nd & 3rd, etc.

See Solution in Thread

2 Replies 2
Tim_Sullivan
7 - App Architect
7 - App Architect

Just thinking out loud here, but what if you tested it backwards? If the FP 4th field isn’t blank, concatenate them all, if it is blank, look to FP 3rd and do the same thing but only concatenate the FP 1st, 2nd & 3rd, etc.

That worked! Well, once I got all of my commas in the right place it did. Thank you!