Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
Me thinks there must be a better way of importing the data :)
Steve On Fri, 01 Sep 2006 15:34:21 +0100, Pete_UK wrote: 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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
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 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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
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 "David" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
Today for us in the USA. <vbg
Gord Dibben wrote: 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 -- Dave Peterson |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
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" wrote in message ups.com... 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 |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
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 Gord Dibben wrote: 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 |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
Yep. Starting points for instructions can be difficult to pinpoint. I used to
joke to my co-workers that I really don't want to tell them how to set their alarm clocks and get ready for work--much less document their path to work--or how to find the on/off switch on the pc. But I read Gord's instructions and they seemed quite reasonable to me <bg. If I were you, I'd revisit his instructions and try it once more. If you have questions, post back with the steps you followed (which could be difficult to document <vbg). You may find that Gord's function useful later--or you may find that you need something different that can't be done in MSWord. Just a thought (or two). David wrote: 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" wrote in message ups.com... 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 -- Dave Peterson |
#22
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
Hi David,
To me it looks like you are trying to create a .csv (Comma Separated Values) file, which Excel is perfectly able to create with a File, Save As and for more troublesome stuff you can use a macro Comma Separated Values, .CSV files http://www.mvps.org/dmcritchie/excel/csv.htm Are you Sure you need everything in one cell. After you get everything in one cell, what do you do with it. If it is simply paste it into notepad then you could have created the flat file directly out of Excel. Create CSV files from each sheet in selection, Dave Peterson, 2005-09-15, public.excel http://groups.google.com/groups?thre...zonXSPAM.ne t =-- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "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 |
#23
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
But saving as a .csv file, which I tried, just seemed to produce a file
which, when opened, looked exactly the same as the .xls, with everything in cells. OK, I now know that if I rename the csv to a txt, I can open it in wordpad and get my data in a column without cells, copy it all and paste special it as unformatted text. But that's what I did anyway, using word. I need the data in nine cells because it's used to generate a table in a sql database where a website will check a custoemrs postcode and, depending on which column the postcode occurs in, it can apply a shipping charge appropriate for listed quantity ordered. Cheers 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 "David McRitchie" wrote in message ... Hi David, To me it looks like you are trying to create a .csv (Comma Separated Values) file, which Excel is perfectly able to create with a File, Save As and for more troublesome stuff you can use a macro Comma Separated Values, .CSV files http://www.mvps.org/dmcritchie/excel/csv.htm Are you Sure you need everything in one cell. After you get everything in one cell, what do you do with it. If it is simply paste it into notepad then you could have created the flat file directly out of Excel. Create CSV files from each sheet in selection, Dave Peterson, 2005-09-15, public.excel http://groups.google.com/groups?thre...zonXSPAM.ne t =-- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "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 |
#24
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
On Fri, 01 Sep 2006 21:06:06 +0100, David wrote:
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! if you were going to use word you could have just replace *newline* with ", " -- Steve (3) |
#25
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Merging Cells
So your database will only have 9 entries - fair enough
Just all seems odd manipulation of data for end result. Steve On Sat, 02 Sep 2006 19:36:53 +0100, David wrote: But saving as a .csv file, which I tried, just seemed to produce a file which, when opened, looked exactly the same as the .xls, with everything in cells. OK, I now know that if I rename the csv to a txt, I can open it in wordpad and get my data in a column without cells, copy it all and paste special it as unformatted text. But that's what I did anyway, using word. I need the data in nine cells because it's used to generate a table in a sql database where a website will check a custoemrs postcode and, depending on which column the postcode occurs in, it can apply a shipping charge appropriate for listed quantity ordered. Cheers David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Merging and unmerging cells | Excel Worksheet Functions | |||
Merging cells with the same values | Excel Discussion (Misc queries) | |||
Merging Two Cells and Keeping the Values | Excel Discussion (Misc queries) | |||
Merging cells with text as one line | Excel Worksheet Functions |