Joining Text From Multiple Cells

[Jeff]

Hi everyone,

I have a large number of cells containing text in a
worksheet that I need to join into a long string of text
in a single cell. Other than using the Concantonate
function, does anyone know of a simpler way to do this
through a single formula?

Thanks !

Jeff

 

[Laura Cook]

Not sure if it is simpler...

=A1&B1&C1&D1&E1

 

[Jeff]

Gord,

I can't thank you enough !!! Works great !!

If there is anything I can do to advise you on Wireless or
RF Communications Systems, please don't hesitate to ask.

Jeff

-----Original Message-----
Jeff

If cells are contiguous........

Function ConRange(CellBlock As Range) As String
Application.Volatile True
For Each cell In CellBlock
ConRange = ConRange & cell.Value & " "
Next
End Function

Copy/paste to a general module in your workbook.

Usage is: =ConRange(A1:A10)

Otherwise go with Laura's suggestion.

Gord Dibben Excel MVP XL2002

 

[Jeff]

Laura,
Thank you for your post. Check out the post from Gord,
this works great !! Saved me a ton of typing

Jeff

 

[Gord Dibben]

Can't think of anything currently but will keep a copy of this message for
future reference.

Thanks, Gord

Gord,

I can't thank you enough !!! Works great !!

If there is anything I can do to advise you on Wireless or
RF Communications Systems, please don't hesitate to ask.

Jeff