Concatenation problem

**starguy** · 09-27-2006, 03:18 AM

I have data in four cells i-e M4, N4, O4, P4. Data in these cells is result of formulas in them. Sometimes all formulas return values and sometimes only first or first two returns values and others remain blank.
(blank cell will always be the most right cell)
I want to put a formula in a single cell that will join all values appearing in above four cells but each should be seperated with a comma and a space.

Regards.

**VBA Noob** · 09-27-2006, 03:26 AM

Hi,

Does this work for you

=IF(COUNTBLANK(M4:P4)=4,"",IF(COUNTBLANK(M4:P4)=3,M4,IF(COUNTBLANK(M4:P4)=2,M4&" ,"&N4,IF(COUNTBLANK(M4:P4)=1,M4&" ,"&N4&" ,"&O4,M4&" ,"&N4&" ,"&O4&" ,"&P4))))

VBA Noob

**Bryan Hessey** · 09-27-2006, 03:52 AM

Originally Posted by VBA Noob

Hi,

Does this work for you

=IF(COUNTBLANK(M4:P4)=4,"",IF(COUNTBLANK(M4:P4)=3,M4,IF(COUNTBLANK(M4:P4)=2,M4&" ,"&N4,IF(COUNTBLANK(M4:P4)=1,M4&" ,"&N4&" ,"&O4,M4&" ,"&N4&" ,"&O4&" ,"&P4))))

VBA Noob

Having re-read the question, VBA - you need a comma-space and not as shown space-comma, otherwise works ok.

---

**starguy** · 09-27-2006, 04:04 AM

Originally Posted by VBA Noob

Hi,

Does this work for you

=IF(COUNTBLANK(M4:P4)=4,"",IF(COUNTBLANK(M4:P4)=3,M4,IF(COUNTBLANK(M4:P4)=2,M4&" ,"&N4,IF(COUNTBLANK(M4:P4)=1,M4&" ,"&N4&" ,"&O4,M4&" ,"&N4&" ,"&O4&" ,"&P4))))

VBA Noob

thank you it works for me.

Bryan Hessey's formula leaves a comma at the end of the value.

**Bryan Hessey** · 09-27-2006, 05:02 AM

Originally Posted by starguy

thank you it works for me.

Bryan Hessey's formula leaves a comma at the end of the value.

Hi Starguy, -
I mis-read your question and assumed ANY cell could be blank, thus the formula was entered in two parts, the S and T cells, the S may leave a trailing comma, the T removed it.

However, a re-read said that only the last cell(s) would be blank, so the more simple formula as per VB Noob worked well. (hence my post was withdrawn)

Cheers.