Remove Ending commas from formula result

[gmooney]

I have the following formula that generates the following result

=UPPER(AW171)&" - "&"including the following Subcategories" & PROPER(" - "&AW172&", "&AX172&", "&AY172&", "&AZ172&", "&BA172&", "&BB172&", "&BC172&", "&BD172&", "&BE172&", "&BF172&", "&BG172&", "&BH172&", "&BI172)

PACKAGED BEVERAGES - including the following Subcategories - Bottled Water, Carbonated Soft Drinks, Energy Drinks, Enhanced Water, Iced Tea Ready To Drink, Other Packaged Beverages, Sports Drinks, , , , , ,

I need to remove any commas at the end because as cell AW171 (PACKAGED BEVERAGES) gets changed to something else the resulting values in the columns of 172 will not always be the same number of real values. Some categories have 2 or 3 sub categories while others have 10.

The end result would like this below where the string doesn't end in any commas.

PACKAGED BEVERAGES - including the following Subcategories - Bottled Water, Carbonated Soft Drinks, Energy Drinks, Enhanced Water, Iced Tea Ready To Drink, Other Packaged Beverages, Sports Drinks

Any ideas?

 

[Fluff]

How about

=UPPER(AW171)&" - "&"including the following Subcategories - " & PROPER(TEXTJOIN(", ",,AW172:BI172))

 

[gmooney]

How about

=UPPER(AW171)&" - "&"including the following Subcategories - " & PROPER(TEXTJOIN(", ",,AW172:BI172))

VERY SLICK. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JackDanIce]

Try:

=UPPER(A171)&" - including the following Subcategories - "&PROPER(TEXTJOIN(", ",1,AW172:BI172))

 

[Fluff]

@JackDanIce See post#2.
You also seem to have a stray ! in there.

 

[JackDanIce]

Haha thanks @Fluff, nice spot and I posted my suggestion without seeing there had been updates to the thread!

 

[gmooney]

Haha thanks @Fluff, nice spot and I posted my suggestion without seeing there had been updates to the thread!

2 great minds think alike. Thanks for the help!