David

You say "many" to a single cell.

You can combine data from many cells to one cell by using a formula like

=A1&B1&C1&D1&E1&F1 etc.

If "many" is a great whack you might do better with a User Defined Function.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is =ConCatRange(Sheet1!A1:A43) or your choice.

I would not use this on more than about 200 cells at a time. Excel won't show
all the characters past about 1000 characters.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula as shown above.


Gord Dibben Excel MVP


On Thu, 31 Aug 2006 22:53:38 +0100, "David" wrote:

Hi

I have a list of a couple of thousand postcodes in a column in a
spreadsheet. Each postcode occupies its own cell. Examples of each postcode
might be AB10 or AB11 or AB12 etc. I want to cut and paste these postcodes
to another sheet, many codes to be pasted into single cells.

I tried to merge the cells as they stand so that I could collectively copy
and paste them but excel says that the cells contain multiple data values
and won;t let me merge them.

Can anyone tell me how to do this without cutting and pasting the contents
of each cell, one at a time please?

Many thanks
Dave


Gord Dibben MS Excel MVP