Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
Hello!
I am needing to combine a row of cells into one cell. Say, for example, A1:A500 are all zip codes. How can I combine all the 500 zip codes into one cell, separated by a semicolon? Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
Not sure why you would need to do this.
But...........This UDF will do it for you. 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) - 2) End Function Note: you most likely will not see all the characters displayed in the cell due to Excel's 1024 character display limit. However, the data will all be seen in the formula bar. =ConCatRange(A1:A500) To increase the 1024 limit, paste the formula results as values then manually add and Alt + Enter linefeed every 100 or so characters. Gord Dibben MS Excel MVP On Thu, 21 Jan 2010 13:31:15 -0800, Lisa L <Lisa wrote: Hello! I am needing to combine a row of cells into one cell. Say, for example, A1:A500 are all zip codes. How can I combine all the 500 zip codes into one cell, separated by a semicolon? Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
A little tinker to try ..
Copy A1:A500, paste into Notepad Copy from Notepad, paste into formula bar for B1 Put in C1: =SUBSTITUTE(B1,CHAR(10),";") Copy C1, paste into Notepad. Done in 10 sec Success? celebrate it, hit the YES below -- Max Singapore --- "Lisa L" wrote: I am needing to combine a row of cells into one cell. Say, for example, A1:A500 are all zip codes. How can I combine all the 500 zip codes into one cell, separated by a semicolon? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
This works great! Thank you for your assistance! Happy Friday!
"Max" wrote: A little tinker to try .. Copy A1:A500, paste into Notepad Copy from Notepad, paste into formula bar for B1 Put in C1: =SUBSTITUTE(B1,CHAR(10),";") Copy C1, paste into Notepad. Done in 10 sec Success? celebrate it, hit the YES below -- Max Singapore --- "Lisa L" wrote: I am needing to combine a row of cells into one cell. Say, for example, A1:A500 are all zip codes. How can I combine all the 500 zip codes into one cell, separated by a semicolon? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
Max's response above worked for me. I do appreciate your response though!
PS...the reason I needed to do this is because I have to paste the zip codes with a semi colon between each one, into a different program. I was hoping not to have to type them! Happy Friday! "Gord Dibben" wrote: Not sure why you would need to do this. But...........This UDF will do it for you. 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) - 2) End Function Note: you most likely will not see all the characters displayed in the cell due to Excel's 1024 character display limit. However, the data will all be seen in the formula bar. =ConCatRange(A1:A500) To increase the 1024 limit, paste the formula results as values then manually add and Alt + Enter linefeed every 100 or so characters. Gord Dibben MS Excel MVP On Thu, 21 Jan 2010 13:31:15 -0800, Lisa L <Lisa wrote: Hello! I am needing to combine a row of cells into one cell. Say, for example, A1:A500 are all zip codes. How can I combine all the 500 zip codes into one cell, separated by a semicolon? Thank you! . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
Hello Gord,
... Note: *you most likely will not see all the characters displayed in the cell due to Excel's 1024 character display limit. ... 1024 characters? http://www.xlam.ch/xlimits/ shows [Zeichen = characters] per cell: Excel 2.x: 255 Zeichen Excel 3.0: 255 Zeichen Excel 4.0: 255 Zeichen Excel 5.0: 255 Zeichen Excel 7.0/95: 255 Zeichen Excel 97: 32'000 Zeichen = 32'767 Zeichen Excel 2000: 32'767 Zeichen Excel 2002: 32'767 Zeichen Excel 2003: 32'767 Zeichen Excel 2007: 32'767 Zeichen Regards, Bernd |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining cells with separater
Welcome, Lisa. Glad to hear. Think you forgot to hit the YES below in that
earlier response .. -- Max Singapore --- "Lisa L" wrote: This works great! Thank you for your assistance! Happy Friday! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Cells | Excel Discussion (Misc queries) | |||
Need help with combining cells to one | Excel Discussion (Misc queries) | |||
Combining 2 cells | Excel Worksheet Functions | |||
combining cells | Excel Worksheet Functions | |||
combining cells | Excel Discussion (Misc queries) |