How do I append data in several cells in one column

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

Explain your appendage.

--
Don Guillett
SalesAid Software
donaldb@281.com
"Tim" <Tim@discussions.microsoft.com> wrote in message
news:D2F182DD-6296-4326-A7C7-29A08E8DC0A7@microsoft.com...
> 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..

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

On Wed, 16 Nov 2005 12:40:08 -0800, Tim <Tim@discussions.microsoft.com> wrote:

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

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.

"Tim" wrote:

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

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

"Suresh" wrote:

> 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.
>
> "Tim" wrote:
>
> > 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..