Help with Formula: Remove trailing commas or using IF to remove results from blank cells?

I am using a formula to pull results from multiple cells and concatenate them, place a comma between, each entry, and remove extra spaces so I can import the csv into a database.
Basically, I am trying to take text entries from multiple columns and put them together with comma separators.
So if...
Column AZ2=Visa
Column BA2=Mastercard
Column BB2=Amex
Column BC2=Discover
Results = Visa,Mastercard,Amex,Discover

I am using the following formula:

=SUBSTITUTE(TRIM(AZ2&","&BA2&","&BB2&","&BC2),",,",",")

This works fine EXCEPT that if the last field, or all fields, are blank the formula renders the comma anyway.

So lets say that BB2 and BC2 is empty.
Instead of..
Visa,Mastercard I get Visa,Mastercard,
..and that trailing comma is keeping the records from importing.
Additionally, if all fields are empty instead of an empty result I get ,,

Is there something I can do to fix this formula or can I use IF to skip blank cells? (I'm lost on the syntax)

Awesome! Worked like a charm. Thanks!

One small problem…

It is also replacing spaces between words in the same cell with commas>
American Express becomes American,Express

I just shortened it to AmEx for now but am looking for a better solution.

Originally Posted by AlKey

Then try this

=SUBSTITUTE((SUBSTITUTE(TRIM(CONCATENATE(AZ2&" ",BA2&" ",BB2&" "&BC2))," ",", ")),"American, Express","American Express")

That did the trick. Thanks!

I'm also having a similar issue with the formula replacing spaces between words in the same cell but I have many different occurrences so I cannot simply do the substitution at end of the formula as you did for American,Express and American Express... Any ideas? Thx

Hi

This solution was very useful!

However, is there a way to change the formula so that there is an "&" sign visible in the cell before the last entry?

So in the example below, where BB2 and BC2 are blank, the results would be Visa, Mastercard & Amex

Column AZ2=Visa
Column BA2=Mastercard
Column BB2=Amex
Column BC2=
Results = Visa, Mastercard & Amex