If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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
|
|||
|
|||
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 |
#7
|
|||
|
|||
Merging Cells
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 "Dave Peterson" wrote in message ... 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 |
#8
|
|||
|
|||
Merging Cells
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 On Fri, 1 Sep 2006 09:33:51 +0100, "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 Gord Dibben MS Excel MVP |
#9
|
|||
|
|||
Merging Cells
Too late, Gord, he's already done it using Word. Anyway, Dave told him
that yesterday. Pete Gord Dibben wrote: 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 On Fri, 1 Sep 2006 09:33:51 +0100, "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 Gord Dibben MS Excel MVP |
#10
|
|||
|
|||
Merging Cells
Yesterday!!
Where has the time gone?g I'm definitely getting lapped. Gord On 1 Sep 2006 14:47:30 -0700, "Pete_UK" wrote: Too late, Gord, he's already done it using Word. Anyway, Dave told him that yesterday. Pete Gord Dibben wrote: 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 On Fri, 1 Sep 2006 09:33:51 +0100, "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 Gord Dibben MS Excel MVP |
Thread Tools | |
Display Modes | |
|
|