antimel
asked on
Concatenate text and insert carriage returns using macro
I would like to be able to select text from several rows or columns, then run a macro that will combine the cells into one cell with a carriage return CHAR(10) inserted between the text from each cell. I've seen that this can be done using a formula, but i would prefer to just select the text and hit a button. Then the macro would prompt me to paste is somewhere or alternatively, it could just enter all the text with the carriage returns into the first cell i selected.
Just select a range of cells and run this code. It will display the result on a mesage box. You can put it on a place on your sheet if you want.
jppinto
jppinto
Sub JoinCells()
Dim result As String
Dim i As Long
For i = 1 To Selection.Cells.Count
If Selection.Cells(i, 1).Value <> "" Then
result = result & Selection.Cells(i, 1).Value & Chr(10)
End If
Next i
MsgBox (result)
End Sub
ASKER
Zorvek, your solution didn't appear to do anything when i ran it.
ippinto, how do you paste the data onto the sheet instead of showing a message box.
ippinto, how do you paste the data onto the sheet instead of showing a message box.
You have to put the code in the worksheet code module, not a general code module.
Kevin
Kevin
ASKER
Zorvek,
I have built a add-in where i keep all my macros, i'd like to add it to that so i can run this on any excel document. Is there a way to do that. I have about a hundred different excel files where i need to use this macro.
I have built a add-in where i keep all my macros, i'd like to add it to that so i can run this on any excel document. Is there a way to do that. I have about a hundred different excel files where i need to use this macro.
A different approach. Select the cells, run the macro below, select the target cell (double click to enter text) and press CTRL+V.
Public Sub CopyCellsToClipboard()
Dim Clipboard As Object
Dim Cell As Range
Dim Value As String
Set Clipboard = GetObject("New:{1C3B4210-F 441-11CE-B 9EA-00AA00 6B1A69}")
Value = vbNullString
For Each Cell In Selection
Value = Value & IIf(Len(Value) > 0, vbLf, vbNullString) & Cell
Next Cell
Clipboard.SetText Value
Clipboard.PutInClipboard
End Sub
Kevin
Public Sub CopyCellsToClipboard()
Dim Clipboard As Object
Dim Cell As Range
Dim Value As String
Set Clipboard = GetObject("New:{1C3B4210-F
Value = vbNullString
For Each Cell In Selection
Value = Value & IIf(Len(Value) > 0, vbLf, vbNullString) & Cell
Next Cell
Clipboard.SetText Value
Clipboard.PutInClipboard
End Sub
Kevin
Something like this...
Sub JoinCells()
Dim result As String
Dim i As Long
For i = 1 To Selection.Cells.Count
If Selection.Cells(i, 1).Value <> "" Then
If i <> Selection.Cells.Count Then
result = result & Selection.Cells(i, 1).Value & Chr(10)
Else
result = result & Selection.Cells(i, 1).Value
End If
End If
Next i
Cells(1, 10).Value = result
End Sub
ASKER
ippinto, It seems to just paste into J1, can i shoose where to paste it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are using Excel, which you hinted to in your zone you chose, you can use the following
=CONCATENATE(A1,CHAR(10),B 1)
This would combine the text in A1 and B1 with a carriage return in between
Note: This wont work unless you have wrap text on for the cell you are using to combine them.
=CONCATENATE(A1,CHAR(10),B
This would combine the text in A1 and B1 with a carriage return in between
Note: This wont work unless you have wrap text on for the cell you are using to combine them.
"I've seen that this can be done using a formula, but i would prefer to just select the text and hit a button."
My mistake, didnt read the whole thing.
ASKER
An elegant solution, it worked perfectly, and I added it to my macro add-in. Thank you for helping me out on this.
Anthony
Anthony
Private Value As String
Public Sub Step1()
Dim Cell As Range
Value = vbNullString
For Each Cell In Selection
Value = Value & IIf(Len(Value) > 0, vbLf, vbNullString) & Cell
Next Cell
End Sub
Private Sub Worksheet_SelectionChange(
If Target.Cells.Count = 1 Then
If Len(Value) > 0 Then
ActiveCell.Value = Value
Value = vbNullString
End If
End If
End Sub
Kevin