Macro to merge text (Any)

Topic

New Reply

I have Excel worksheet that contains a columns with text. Sometimes there is only one cell in the row with text (say, in A2), sometimes there are two two cells (say, A3 “From the beginning”, B3 “to the End”), sometimes three cells (A4 “From”, B4 “the beginning”, C4 “to the End”). Of course, the text is different from cell to cell. I need to write a macro that merge text in one cell (A4 “From the beginning to the End”, B4 and C4 blank) and do it automatically through all column (A). Any help will be greatly appreciated.

Reply | Quote

Replies

What rows do you want to do this for? Starting at what row? Stopping at what row. Do you always want to stop at column C, or continue if there is something in columns past column C? If there is something in column A and C, but column B is empty, what do you want to do?

Reply | Quote

I want to do it in all non-empty rows: from 2 to 1275, or 1836, or whatever until the cell in column A will be empty. If there is nothing in column B, there is also nothing in column C. There is nothing in column D etc. (The worksheet was created from the text file, last column of which, converted into column A in the worksheet, contained text sometimes with hard returns to wrap it. Text in any cell can start either with capital or lowercase letter. There were too many entries to remove hard returns manually. But I need all text to be in one cell. Blank spaces at the end are not importamt.)

Reply | Quote

Something like this?

Sub MergeABC()
Dim aRange As Range
Dim aCell As Range

On Error GoTo Exit_Sub

Set aRange = Range(Range(“A2”), Range(“A65536”).End(xlUp))
For Each aCell In aRange
If aCell.Offset(0, 1) “” Then
aCell = aCell & ” ” & aCell.Offset(0, 1)
If aCell.Offset(0, 2) “” Then
aCell = aCell & ” ” & aCell.Offset(0, 2)
End If
End If
Next

aRange.Offset(0, 1).Clear
aRange.Offset(0, 2).Clear

Exit_Sub:
Set aCell = Nothing
Set aRange = Nothing
End Sub

Reply | Quote

And what about this:

Sub MergeABC()
Dim aRange As Range
Dim aCell As Range

On Error GoTo Exit_Sub

Set aRange = Range(Range(“A2”), Range(“A65536″).End(xlUp))
For Each aCell In aRange
aCell = Trim(aCell & ” ” & aCell.Offset(0, 1) & ” ” & aCell.Offset(0, 2))
Next

aRange.Offset(0, 1).Clear
aRange.Offset(0, 2).Clear

Exit_Sub:
Set aCell = Nothing
Set aRange = Nothing
End Sub

Reply | Quote

Thank you very much

Reply | Quote

Hi Servando,

Yes, that’s more efficient. Since the case where column B is empy while column A and C are filled should not occur, it should work correctly.

Reply | Quote

How are you going to determine which cells to merge? Will the first cell of a “sentence” always begin with an upper case letter, and the subsequent cells with something else? How about

	A	B
1	I love	New York

Will there always be an empty cell after a “sentence”?

Reply | Quote