Function(s) to add or delete comma or space based on variable

Hello all,

I'm trying to setup a system where commas will automatically be added (or subtracted) based on if a cell is empty or not. For Example:

A1: Black
A2: Metal
A3: Shelf

A5: Black, Metal, Shelf

But if it reads:

A1: Black
A2: Metal
A3:

A5: Black, Metal

or alternatively:

A1: Black
A2:
A3: Shelf

A5: Black, Shelf

How do I go about doing this? Trial and error hasn't provided me with a result

Thanks!

In A5

=SUBSTITUTE(TRIM(CONCATENATE(A1&" "&A2&" "&A3))," ",", ")

Row\Col	A
1	Black
2	Metal
3	Shelf
4
5	Black, Metal, Shelf

and

Row\Col	A
1	Black
2
3	Shelf
4
5	Black, Shelf

Or simply,
=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3)," ",", ")

taking into account the there may be multiple words in a single cell, this wold work as well
EDIT: will change second formula

Thank you for the feedback

BTW, you don't need another formula to accommodate cells with more than one word. The formula provided will do it just fine

Example:

Row\Col	A
1	Black
2	Metal Silver Gold
3	Shelf
4
5	Black, Metal, Silver, Gold, Shelf

The best way do deal with concatenating values would be to use VBA. Here is what you need to do:

1. Copy code below
2. Press Alt and F11 on your keyboard to open VB Editor
3. Click on Insert and select Module
4. Paste code into Module and close VB Editor.

Don't forget to save your worbook as Macro-Enabled workbook.

Please Login or Register  to view this content.

then use custom formula

=Concat_Range(A1:A5,", ")

Row\Col	A
1	Black
2	Metal*
3	Shelf*
4	Large Size
5	In stock in NYC
6
7	Black, Metal*, Shelf*, Large Size, In stock in NYC

Use "&" with if condition as in attached
In Column formula in the last cell is different
thanks

Originally Posted by berlan

A quick but rather manual way,

Please Login or Register  to view this content.

Awesome, that's exactly what I was looking for

One more quick question:

What if I want text to be automatically added if the cell is populated? Ex:

A1: 24
A2: shelf
A3: black

A5: 24 part, shelf

alternatively:

A1:
A2: shelf
A3: black

A5: shelf, black

Thanks again!