Solved: How to Concatenate Text

[Cyberdude]

Given that I have two text strings . . . one in cell ?A1? and the other in cell ?A2?. I want to manually copy the string in cell ?A2? and insert it at the end of the string in cell ?A1?. Something like this:
In ?A1? I have ?Hello there?.
In ?A2? I have ? good friend.?.
I want to construct in ?A1? the string ?Hello there good friend.?

I know, why don?t I tust type it in? Because sometimes I have a lot of these to construct. Other times the string in ?A2? might be quite long.

Here?s what happens:
Step 1: Click on cell ?A2? .
Step 2: Click on the icon at the top of my screen that I use to copy.
Step 3: Click on cell ?A1?
Step 4: Move my cursor to the end of the string in cell ?A1? and click it.
When I do that, the icon that I use to paste is suddenly grayed out, so I can?t paste what I copied.

What rule am I violating?

[malik641]

When you select a cell and click the copy icon, you are copying a Range object, and not just the text. So when you click in the formula bar after clicking A1, you lose the Paste icon because I guess excel cannot handle understanding you want to paste the .Value property into the location that you click at.

So what you should do is click cell A2, then click the formula bar and select the text and THEN click the Copy icon. Then click cell A1 and click in the formula bar and the Paste icon WILL be available to you and you can paste the text you copied.

In addition, I believe Excel uses it's own Clipboard object to store Class objects, and when you click the formula bar, it's using the system's clipboard. Again, this is what I think.

[mdmackillop]

Hi Sid,
Here's a short macro I use for that requirement

[VBA]Sub JoinCells()
Dim txt As String, cel As Range
For Each cel In Selection
txt = txt & cel & " "
Next
txt = Left(txt, Len(txt) - 1)
Selection.ClearContents
Selection(1) = txt
End Sub[/VBA]

[Bob Phillips]

Snap! Here's mine

[vba]

Private Sub JoinColumns()
Dim LastCol As Long
Dim cell As Range
Dim j As Long
For Each cell In Selection
With cell
.Value = .Value & " " & .Offset(0, 1).Value
.Offset(0, 1).Value = ""
LastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column
For j = .Column + 1 To LastCol - 1
Cells(.Row, j).Value = Cells(.Row, j + 1).Value
Cells(.Row, j + 1).Value = ""
Next j
End With
Next cell
End Sub
[/vba]

Mine will process multiple rows, if selected.

I also have one for trimming the tail of a selection

[vba]

Private Sub TrimTail()
Dim cell As Range
Dim i As Long
For Each cell In Selection
i = Len(cell.Value)
Do Until i = 1 Or (Mid(cell.Value, i, 1) <> " " And Asc(Mid(cell.Value, i, 1)) <> 160)
i = i - 1
Loop
cell.Value = Left(cell.Value, i)
Next cell
End Sub
[/vba]

and of course one to break up the other way

[vba]

Sub TextToColumns()
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1))
End Sub
[/vba]

Why all of this nonsense? Because I often copy data in a worksheet to test for problems, and it is often drawn in as one string, especially from NGs, and so I pull it in, split it, then tidy it up. Those buttons save me hours.

[Cyberdude]

Thanx, Gang! Your suggestions are a big help. Who would have guessed that such a simple operation could be so complicated.