Combine Text From Cells

[nhbcai]

I have column of product codes that I need to insert into a field in my shopping cart. There are about 600 of them and the text in each field needs to be separated by commas.

I thought Concatenate may be a good idea, but that's a heck of a lot of clicking. haha

Does anyone know how I can join the text in all the cells into one field that is comma delimited.

To illustrate:

Column A
EC-001
EC-002
EC-003
EC-004
EC-005

What I need is data joined too look like this:
EC-001,EC-002,EC-003,EC004,EC-005

But there are like 650 of them. Literally.

Any ideas?

I've gotten a lot of help from this forum for free. And I don't take that lightly.

Thanks.

 

[GlennUK]

How are you going to insert the field into the shopping cart exactly? Will it take such a large string ( well in excess of 4000 characters )?

In the meantime, here's a UDF to do the concatenation:

Function mconcatmine(myrange As Range) As String
    mconcatmine = ""
    For Each c In myrange
        If Not IsEmpty(c) Then
            mconcatmine = mconcatmine & c.Value & ","
        End If
    Next
    If Len(mconcatmine) > 0 Then mconcatmine = Left(mconcatmine, Len(mconcatmine) - 1)
    
End Function

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG30Jun51
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.count).End(xlUp))
Range("B1") = Join(Evaluate("transpose(" & Rng.Address & ")"), ", ")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] HW()
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    arr = Range("A1:A" & Range("A1").End(xlDown).Row)
    [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr, 1)
        Activcell = ActiveCell & arr(i) & IIf(i = [COLOR="Blue"]UBound[/COLOR](arr, 1), "", ",")
    [COLOR="Blue"]Next[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]