Merging Columns, keeping text

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

Hi,
Something along these lines:

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

HTH

"cbrd" wrote:

>
> 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
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500406
>
>

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 wrote:
>
> 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
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500406

--

Dave Peterson

Ignore this post. I misread merging.

Dave Peterson wrote:
>
> 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 wrote:
> >
> > 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
> >
> > --
> > cbrd
> > ------------------------------------------------------------------------
> > cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> > View this thread: http://www.excelforum.com/showthread...hreadid=500406
>
> --
>
> Dave Peterson

--

Dave Peterson

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

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")



cbrd wrote:
>
> Thanks for the help. That doesnt seem to be doing anything though.
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=500406

--

Dave Peterson