Need some help in creating a formula to combine cells

[RyeGuy]

Basically I have a data set that runs across 40+ columns. Its tough for me to explain what it is I am trying to do so the example below hopeful gives some pictoral to it.


As new data gets added I need to pull them int a separate sheet to list specific columns.

1 ABCDEFGHIJKLMNOPQESTUVWXYZ
2 ABCDEFGHIJKLMNOPQESTUVWXYZ
3 ABCDEFGHIJKLMNOPQESTUVWXYZ
4 ABCDEFGHIJKLMNOPQESTUVWXYZ

1 ABCD
2 LMNO
3 WXYZ
4 ABCD


Any help here would be greatly appreciated. Happy Holidays!

 

[Korbi]

Hey all,
I need to marge multiple cells in one cell separated with a comma.
I did this:
=C3&","&D3&","&E3&","&F3&","&G3&","&H3&","&I3&","&J3&","&H3&","&K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3
this is the result

Astab-/Astausbruch,,Astungswunden/-fäule,,,,Höhlungen,,,,,,,Totholz,, (there are blank cells so got those ",,,,,")

<tbody>
</tbody>

how can i skip the blank cells?
to get something like this:

Astab-/Astausbruch,Astungswunden/-fäule,Höhlungen,Totholz, Thanks

<tbody>
</tbody>

 

[Korbi]

Hey all,
I need to marge multiple cells in one cell separated with a comma.
I did this:
=C3&","&D3&","&E3&","&F3&","&G3&","&H3&","&I3&","&J3&","&H3&","&K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3
this is the result

Astab-/Astausbruch,,Astungswunden/-fäule,,,,Höhlungen,,,,,,,Totholz,, (there are blank cells so got those ",,,,,")

<tbody>
</tbody>

how can i skip the blank cells?
to get something like this:

Astab-/Astausbruch,Astungswunden/-fäule,Höhlungen,Totholz, Thanks

<tbody>
</tbody>

I also try this:

[/COLOR]Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice 20151103
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & ","): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function
[COLOR=#574123]

but if there in none data in the cells from C3 to Q3 i get as result: #VALUE!