How do I append data in several cells in one column

T

Tim

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..
 
G

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
 
S

Suresh

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.
 
T

Tim

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