Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- cbrd ------------------------------------------------------------------------ cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009 View this thread: http://www.excelforum.com/showthread...hreadid=500406 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Columns, keeping text
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Columns, keeping text
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Columns, keeping text
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Columns, keeping text
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Columns, keeping text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging text columns | Excel Discussion (Misc queries) | |||
Merging two columns and keeping the data from both | Excel Worksheet Functions | |||
merging text columns | Excel Discussion (Misc queries) | |||
Merging 3 Columns in an Excel Spreadsheet (Keeping Data Intact)...? | Excel Programming | |||
Merging 3 Columns in an Excel Spreadsheet (Keeping Data Intact)...2nd Post | Excel Programming |