How can I concatenate non blank cells in a series

**joshuar** · 12-01-2009, 02:26 AM

Hi All,

I have a problem here for you.
How can I concatenate a series of cells that may or may not have blank cells in them? I do not want to concatenate the blank cells.
I hope I am clear.

Thanks
J

**Paul** · 12-01-2009, 02:36 AM

Hi Joshuar,

Are you looking for a formula approach, or a macro? Also, how many cells are you trying to concatenate? Finally, when concatenating the cells, are you hoping to have a space or some other character between each cell's value?

**joshuar** · 12-01-2009, 02:52 AM

Hi Paul,

I have 11 cells to concatenate and if the cells are nonblank I need to insert a space in between, except for the last cell. As regards, formula or macro maybe you can explain both. I do not have anything specific in mind.

Thks.

**DonkeyOte** · 12-01-2009, 04:46 AM

Not sure I follow ... if it's a simple space delimiter than concatenate with space and use TRIM on resulting output

=TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2)

This will remove superfluous spaces (incl. those appearing mid-string)