How do I append data in several cells in one column

[Guest]

How do I append data in several cells in one column? I have a situation where
I have several worksheet with approximately 2000 records on each worksheet. I
have to append the data in 2 of the several columns of information. I cannot
permanently change the formatting of the documents. I am looking for
something that can be done on a large scale with a minimum of keystrokes. I
have tried concatenation, but when I delete the reference cell, the program
faults out. I would appreciate any help..

 

[Don Guillett]

Explain your appendage.

 

[Gord Dibben]

Tim

After concatenation, copy the cells and Paste Special>Values>OK>Esc.

Then delete the reference cell.

Or use a VBA macro like..........

Sub Add_Text()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Else
For Each Cell In thisrng
Cell.Value = Cell.Value & moretext
Next
End If
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben Excel MVP

 

[Guest]

If Concatenation works, you might paste the concatenated column as values
before deleting the reference cells.

if too many columns have to be combined, you can consider saving it as a
text file and then reopening the text file in excel. This will work only on a
single worksheet.

 

[Guest]

Thank you, that worked like a champ. I think it was one of those forest for
the trees things.