Combining multiple cells into one

[marti]

I have a row of cells with data that needs to be combined into one cell and
be seperated by a semicolon and a space.
Example:

a
b
c
d
e
I want it to read like this: a; b; c; d; e; in 1 cell.

I have been trying to use a text srting like this :
=A1&"; "&A2&A3&"; "&A4...etc
The problem I am running into is that this works fine for a small # of
cells, but I have much larger #s of cells that I need to combine this way and
it will take a long time to type them all in.

Any ideas?
Thanks!
Marti

 

[Gord Dibben]

Copy/paste this UFG to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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(A11)


Gord Dibben MS Excel MVP

 

[Megan]

Is all of your data vertical and not horizontal? I would try using the
concatenate formula. I would set up one cell with the semicolon and another
cell with the space. I would then select the cells to use in your formula
while selecting the "semicolon" and "space" cells in between your numbers.
You can then just drag this formula down.

 

[marti]

Megan
I can't get this to work. I haven;t ever used this formula before. Would you
be able to explain a bit more to me?