MS Excel combine multiple cells of text into 1 cell

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
Jon CarlsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jon CarlsonAuthor Commented:
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
Jon CarlsonAuthor Commented:
quick response,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.