Merging Cells

[David]

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

 

[David]

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

 

[Gord Dibben]

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

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

 

[Gord Dibben]

David

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

To do it without the UDF

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


Gord

 

[David]

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

 

[MartinW]

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

 

[David]

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

 

[Dave Peterson]

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.

 

[Pete_UK]

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

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

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

 

[David]

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

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

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

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

 

[SteveW]

Me thinks there must be a better way of importing the data


Steve

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

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

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

 

[gls858]

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

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

When you say a postcode lookup database. What type of database?
Most database program support importing of various file types.
As one other poster suggested, there must be a better way.
With more info someone here might be able to suggest one.

gls858

 

[David]

I did that. I really did, although this is new to me. I'm not entirely IT
illiterate though and I did try a few intelligent variations, but Excel is
very precise, rightly so, and unless you know what you're doing...anyway, I
couldn't get it to work.

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

 

[David]

I ended up:

creating a second column where each cell contained just a comma and a space.

creating a thrid column where the other two were combined.

Copying the whole third column and pasting it into Word.

Merging the relevant cells in Word

and then special pasting each merged cell contents as unformatted text.

Then each bit was cut and pasted back into each of the 9 cells.

Took all day but it's done!

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

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

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

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


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

 

[Gord Dibben]

David

There is no exclamation mark in my code.

I think that when you copied the code you also copied the Usage instructions.

Just copy the part from

Function ConCatRange(CellBlock As Range) As String

down to and including

End Function


Gord

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

Gord Dibben MS Excel MVP

 

[Pete_UK]

Too late, Gord, he's already done it using Word. Anyway, Dave told him
that yesterday.

Pete

 

[Gord Dibben]

Yesterday!!

Where has the time gone?<g>

I'm definitely getting lapped.


Gord

 

[Dave Peterson]

Today for us in the USA. <vbg>

Yesterday!!

Where has the time gone?<g>

I'm definitely getting lapped.

Gord

 

[David]

I tried it that way, I honestly did. Then reverted to including content that
should, clearly, have been replaced. I entered the phrase 'A2:A150' and also
A2,A150' just to be sure. But, whilst the help and advice of experts such as
yourselves is greatly appreciated, do understand that it's sometimes easy to
forget just what degree of detail is needed in describing how to do
someting. I've been an IT trainer and do have some experience of this.
Imagine telling someone how to drive a car, who's never seen one
before...how would you describe changing gear? In terms of a gearstick? Of
second and fourth etc? What's a gear??

Anyway, I got there in the end. And to my mind, it's a shame that Excel has
so much trouble doing something so simple. Concatenating data in cells. Word
can do it!

Thanks for all your interest.

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

Oh no, it wasn't yesterday - it was about 1:30pm (my time, it being
11:10pm now). It just seems like ages ago. <bg>

Pete