Combining multiple cells into one

M

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
 
G

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(A1:p1)


Gord Dibben MS Excel MVP
 
M

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.
 
M

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top