MS Excel combine multiple cells of text into 1 cell

Jon Carlson
Jon Carlson used Ask the Experts™
For the attached spreadsheet,  I need to do 2 things to be able to import each row into another spreadsheet

1. On the "articles" tab, for each part number in column A I need all the text in column B to be in 1 box but maintain the rows of text (like in a memo field) it can't all run together it must stay in rows within the cell .  It has to be in one cell for the import.

2. The single cell of text from above needs to be copied into the "allparts" spreadsheet into a new column with the corresponding matching part number.  This could be inserted into a new column C right next to column B "EN"

Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
1. I run a macro to combine the text

Sub test()
    Dim ws As Worksheet
    Set ws = Worksheets("Article")
    lastRow = ws.Cells(Worksheets("Article").Rows.Count, "B").End(xlUp).Row
    cpos = 1
    itemID = ""
    tmp = ""
    'Application.ScreenUpdating = False
    For i = 3 To lastRow
        'New item
        If ws.Cells(i, 1) = "" And ws.Cells(i, 2) = "" Then
            ws.Cells(cpos, 3).FormulaR1C1 = tmp
        ElseIf ws.Cells(i, 1) <> itemID And ws.Cells(i, 1) <> "" Then
            cpos = i
            itemID = ws.Cells(i, 1)
            tmp = ws.Cells(i, 2)
            tmp = tmp & Chr(10) & ws.Cells(i, 2)
        End If
        Application.StatusBar = "Running" & String(i Mod 5, ".")
    'Last item
    ws.Cells(cpos, 3).FormulaR1C1 = tmp
    Application.StatusBar = ""
    'Application.ScreenUpdating = True
    Columns("C:C").ColumnWidth = 28
End Sub

Open in new window

2. Later, I just do a vlookup to the text that created in part 1, like:


for more info, pls look attached.


Thank you Ryan, all seems to be working well except for the VLOOKUP onto the "allparts" sheet puts the text that was combined into the single cell (Column C on Article) into a single line and doesn't keep the line by line formatting. Is there a way to keep this format or re-format once I pull the text into the "allparts" sheet?  Do I need to copy the text from the VLOOKUP somehow into another field, like a past-value type of thing? I've tried a few things and can't figure it out.

See attached file with VLOOKUP on column C on sheet:allparts


quick response,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial