To concatenate a certain four cells, I'd use:
=CONCATENATE(A2,",",C2",",D2,",",F2)
This would make it so that...
- A2 = "Matthew"
- C2 = "Mark"
- D2 = "Luke"
- F2 = "John"
would result in Matthew,Mark,Luke,John
.
But we run into problems with something like...
- A2 = "Jesus"
- C2 = ""
- D2 = "Mary"
- F2 = "Joseph"
which would result in Jesus,,Mary,Joseph
.
Here, the extra comma is undesired. Is there a way to gracefully handle this so that all non-blank cells are included in the comma-separated list, while avoiding the addition of unnecessary commas when some cells are blank?
Certainly, this can be done with a fair number of nested IFs, but I really want to avoid that if possible. Can it be done with native Excel functions, or perhaps an array formula? Or would one have to resort to VB Script for something like this?