You could use a User Defined Function and enter a formula.

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(a1:a10,"|") desired delimiter between quotes
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text < "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))
End Function

This goes into a General Module.

See Roger's post for how-tos.


Gord Dibben MS Excel MVP

On Fri, 2 Apr 2010 10:31:01 -0700, Jill
wrote:

I want to merge the text from about 25 columns into one column. It is a
course list so each registrant has a different combination of courses. Is
there a way to do this and not have a bunch of extra spaces wherever someone
does not have a course?