asked on 4/16/2020

combining text into a column

I have the following and need to combine a large data set is there an easy way to do this
example
Column1 Column2
Ren 1 DA-IND
Ren 1 +250
Cap 3
etc
I need in one column end result like Column3
Column3
Ren 1 DA_IND
Ren 1 +250 DA_IND
etc
The data in Column 1 changes
I am afraid to do a find and replace because the info in Column1 same verbage exists in other Columns and this would replace them as well. This is fed into a database and needs to be fed in like my end result in Column 3

Microsoft Excel Microsoft Office

Last Comment

Martin Liss

4/16/2020

Martin Liss

4/16/2020

You can use this macro.

Sub MergeColumns()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

For lngRow = 2 To lngLastRow
    Cells(lngRow, "A") = Cells(lngRow, "A") & " " & Cells(lngRow, "B")
    Cells(lngRow, "B") = ""
Next

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

Open in new window

Martin Liss

4/16/2020

I just re-read your question and I now don't understand why you were concerned about find and replace.

Are you saying that everything in columns 'A' should have DA-IND appended to it?

ASKER CERTIFIED SOLUTION

Hello There

4/16/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Patricia Timm

4/16/2020

ASKER

I am afraid it would affect the entire worksheet but maybe I can do a find and replace on a specific column??? As an example
I have
column1 column2 column3
Lapsed Lapsed DA-IND
I want column2 to look like this after
column2
Lapsed DA-IND
However I do not want column1 affected or changed in any way
For those that are unioque I can use a find and replace

Patricia Timm

4/16/2020

ASKER

Also thanks so much for your help!!!! I have not written a macro in 10 years and need to get this out today. I love the macro idea but would take me a little longer. I will save that for the future

Martin Liss

4/16/2020

If you still need help please attach a sample workbook with "before" and "after" worksheets,