Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 31st 06, 10:53 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 17
Default Merging Cells

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



  #2   Report Post  
Old August 31st 06, 11:16 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 17
Default Merging Cells

I forgot to mention...the postcodes need to be comma delimted.


"David" wrote in message
...
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



  #3   Report Post  
Old August 31st 06, 11:33 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default Merging Cells

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
  #4   Report Post  
Old September 1st 06, 12:12 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default Merging Cells

David

The UDF I posted will give you comma-delimited postal codes.

To do it without the UDF

=A1&","&B1&","&C1&","&D1 etc.


Gord

On Thu, 31 Aug 2006 23:16:01 +0100, "David" wrote:

I forgot to mention...the postcodes need to be comma delimted.


"David" wrote in message
...
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



  #5   Report Post  
Old September 1st 06, 09:33 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 17
Default Merging Cells

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





  #6   Report Post  
Old September 1st 06, 10:04 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 860
Default Merging Cells

Hi David,

I'm not sure I understand your problem.
Why do you want to merge the cells before you copy them?

Surely you can just highlight the entire column right click on it
and select copy, then open your new sheet right click in A1
and select paste.

Or am I missing something here?

HTH
Martin


  #7   Report Post  
Old September 1st 06, 12:54 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 17
Default Merging Cells

....because I want all of the contents of every cell to be combined in to a
single cell, comma delimited. [and preferably with a space as well, after
each comma]. This is for putting the data into a postcode lokup database for
determining shipping costs. About 2900 codes are to be split over 9 cells.


"MartinW" wrote in message
...
Hi David,

I'm not sure I understand your problem.
Why do you want to merge the cells before you copy them?

Surely you can just highlight the entire column right click on it
and select copy, then open your new sheet right click in A1
and select paste.

Or am I missing something here?

HTH
Martin



  #8   Report Post  
Old September 1st 06, 01:33 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Merging Cells

Everything between these two lines in Gord's function:
Function ConCatRange(CellBlock As Range) As String
End Function

goes into that General module in the VBE--including those two lines!

And then you'd use something like:
=ConCatRange(Sheet1!A1:A43)
(from sheet2, say)
or just
=ConCatRange(A1:A43)
from the same sheet.

You may want to take a look at Gord's instructions one more time.

David wrote:

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


--

Dave Peterson
  #9   Report Post  
Old September 1st 06, 03:34 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default Merging Cells

That means you would want to combine about 323 cells into each
composite cell, and with a comma between each postcode (with an average
length of 7 characters, say) this means you will have 2584 characters
in each combined cell.

Pete

David wrote:
...because I want all of the contents of every cell to be combined in to a
single cell, comma delimited. [and preferably with a space as well, after
each comma]. This is for putting the data into a postcode lokup database for
determining shipping costs. About 2900 codes are to be split over 9 cells.


"MartinW" wrote in message
...
Hi David,

I'm not sure I understand your problem.
Why do you want to merge the cells before you copy them?

Surely you can just highlight the entire column right click on it
and select copy, then open your new sheet right click in A1
and select paste.

Or am I missing something here?

HTH
Martin


  #10   Report Post  
Old September 1st 06, 04:25 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 17
Default Merging Cells

Thats right...

As a matter of fact, I've just spent all day doing it manually.

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

"Pete_UK" wrote in message
ups.com...
That means you would want to combine about 323 cells into each
composite cell, and with a comma between each postcode (with an average
length of 7 characters, say) this means you will have 2584 characters
in each combined cell.

Pete

David wrote:
...because I want all of the contents of every cell to be combined in to
a
single cell, comma delimited. [and preferably with a space as well, after
each comma]. This is for putting the data into a postcode lokup database
for
determining shipping costs. About 2900 codes are to be split over 9
cells.


"MartinW" wrote in message
...
Hi David,

I'm not sure I understand your problem.
Why do you want to merge the cells before you copy them?

Surely you can just highlight the entire column right click on it
and select copy, then open your new sheet right click in A1
and select paste.

Or am I missing something here?

HTH
Martin






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Groups of cells between workbooks vnacj-joe Excel Discussion (Misc queries) 4 June 14th 07 05:18 PM
Merging and unmerging cells [email protected] Excel Worksheet Functions 1 April 11th 06 04:19 PM
Merging cells with the same values Iain Excel Discussion (Misc queries) 0 August 8th 05 06:35 PM
Merging Two Cells and Keeping the Values Raymond Excel Discussion (Misc queries) 10 May 30th 05 08:05 PM
Merging cells with text as one line Jim Excel Worksheet Functions 1 December 23rd 04 01:29 AM


All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017