Concatenate Cells

P

peterh

I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL
 
R

ryguy7272

Maybe this is what you want?

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k <> 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function

With your data in cells A1:E1, =mergem(A1:E1)
 
G

Gord Dibben

Ryan

Just a head's up.

In cases where there are any blank cells in the range, your code will add
extra commas.

This revision will ignore blank cells.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function


Gord Dibben MS Excel MVP
 
Top