Merging Columns, keeping text

[cbrd]

Dont know why the other post didnt post any message but....

I have Columns setup like below. I wish for the columns C, D, E to be
meged into one, but keeping the data and putting a space in between. I
wish to do this with a macro/button instead of using pasting a formula
over and over.

I Have:
A B C D E <------Columns
Doe | 123 Main | Reed | PA | 15222
and more follow....

I wish to get this result:

A B C <-------Columns
Doe | 123 Main | Reed, PA 15222
would also need to insert a comma when merging after column C

 

[Guest]

Hi,
Something along these lines:

Range("c1")=Range("C1") & ", " & Range("D1") & " " & Range("E1")

HTH

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArea As Range
Dim myRow As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection

Application.DisplayAlerts = False
For Each myArea In myRng.Areas
If myArea.Columns.Count > 1 Then
For Each myRow In myArea.Rows
myStr = ""
For Each myCell In myRow.Cells
If myCell.Text <> "" Then
myStr = myStr & " " & myCell.Text
End If
Next myCell
myRow.Merge across:=True
myRow.Cells(1).Value = Mid(myStr, 2)
Next myRow
End If
Next myArea
Application.DisplayAlerts = True

End Sub

======
But I'd think twice about doing this. Usually keeping each field in a separate
column makes life much easier. And merged cells cause trouble with
sorting/copy|pasting, too.

Select your range (A1:E99???) and run the macro.

 

[Dave Peterson]

Ignore this post. I misread merging.

Maybe something like:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArea As Range
Dim myRow As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection

Application.DisplayAlerts = False
For Each myArea In myRng.Areas
If myArea.Columns.Count > 1 Then
For Each myRow In myArea.Rows
myStr = ""
For Each myCell In myRow.Cells
If myCell.Text <> "" Then
myStr = myStr & " " & myCell.Text
End If
Next myCell
myRow.Merge across:=True
myRow.Cells(1).Value = Mid(myStr, 2)
Next myRow
End If
Next myArea
Application.DisplayAlerts = True

End Sub

======
But I'd think twice about doing this. Usually keeping each field in a separate
column makes life much easier. And merged cells cause trouble with
sorting/copy|pasting, too.

Select your range (A1:E99???) and run the macro.

 

[cbrd]

Thanks for the help. That doesnt seem to be doing anything though.

 

[Dave Peterson]

Actually, it merged the each cell in each row of the selection. But that's not
what you wanted.

A B C D E <------Columns
Doe | 123 Main | Reed | PA | 15222
and more follow....

I wish to get this result:

A B C <-------Columns
Doe | 123 Main | Reed, PA 15222

insert a new column C (shifting C:E to D:F).

Put this in C1 and drag down the column.
=d1&", "&e1&" "&f1
or
=d1&", "&e1&" "&text(f1,"00000")