Combining cells with separater

[Lisa L]

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!

 

[Gord Dibben]

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

 

[Max]

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

 

[Lisa L]

This works great! Thank you for your assistance! Happy Friday!

 

[Lisa L]

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!

 

[Bernd P]

Hello Gord,

...
Note:  you most likely will not see all the characters displayed in thecell
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

 

[Max]

Welcome, Lisa. Glad to hear. Think you forgot to hit the YES below in that
earlier response ..