Many thanks for this. But I keep getting the message'Compile Error:
Expected: list seperator or )' and the exclamation mark in the code becomes
highlighted.
David
--
David Kitching Msc. Msc.
Managing Director
Natural Deco Ltd.
The Manor
Manor Lane
Loxley
Warwickshire CV35 9JX
UK.
Tel: +44 (0) 1789 470040
Mob: +44 (0) 7799 118518
www.naturaldeco.co.uk
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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