Macro to Merge multiple columns together with separator

[ucsutah]

i am looking for a macro to start on columns c2, d2, e2, f2, g2 and merge those together with a separator of |, and put the results on b2

I know you can do it with =c2&e2 but i have 20,000 products i have to do this to and copy and paste is to much time, and i would like to create a shortcut to start it.

example:


Component Monitor LCD Monitor 20"/20.1" LCD Monitor

Needs to be this.

Component|Monitor|LCD Monitor|20"/20.1" LCD Monitor

thanks

[AlvaroSiza]

Make a copy of your workbook and test on the copy.

Highlight the "B" column cells in which you want the result produced and run the following.

Sub StringIt()

Dim strRange As String
Dim strChar As String

strChar = "|"

   For Each rngCell In Selection
      strRange = CStr(rngCell.Offset(0, 1) & strChar & CStr(rngCell.Offset(0, 2) & strChar & _
                 CStr(rngCell.Offset(0, 3) & strChar & CStr(rngCell.Offset(0, 4) & strChar & _
                 CStr(rngCell.Offset(0, 5))))))

      rngCell.Value = strRange

   Next

End Sub

[ucsutah]

Hi, thanks for getting back to me quickly.

I did as you asked and highlighted the b column and its only producing 1 row on each highlight. However, it is producing the correct results its just not copying and merging all the way down to the end of the column of where the data ends.

Is there a way to produce these results and merge the c,d,e,f,g,h columns/rows and the results of the merge into the b column until the very end of where the values stopped from c,d,e,f,g, and h. It should end about 15,000 rows.

If you need a sample of the data please let me know?

thanks

[JBeaucaire]

I actually don't understand how it's hard to enter the formula in B2:

=C2 & "|" & D2 & "|" & E2 & "|" & F2

....then double click the lower-right corner to autofill that instantly down the whole dataset?

A macro to enter that formula instantly in the whole range:

Sub ConcatData()
Dim LR As Long

    LR = Range("C" & Rows.Count).End(xlUp).Row
    
    With Range("B2:B" & LR)
        .FormulaR1C1 = "=RC[1] & ""|"" & RC[2] & ""|"" & RC[3] & ""|"" & RC[4]"
        .Value = .Value
    End With

End Sub

[ucsutah]

thanks for your help

Yes, your right i just have a lot of other things to do (a large project) and just thought it would be easier and save a little time. Anyway, i have attached on what the vb script did as an output.

The first c,d,e columns merge perfectly but then the next column doesn't merge and creates a duplicate after the c,d,e columns and the f column and g column don't merge.

Do you have an idea on what is wrong?

thanks again for your help and time.

MacrotoMergeColumnsExample.xlsx

[ucsutah]

Ok, its working now i just noticed that rc[2] was on there as a duplicate and it was missing rc[5] and just added that and changed it to rc[3] and it works perfectly now. thanks