Transforming data in column into a linear text string



Hi all,

my data in column A is like


How can I copy that data into a string like "cat,dog,apple" without
having to manually remove the CR/LF and add the comma between the words?

I.E. how can I transform those rows into a single text string?

thanks so much

Luke M

You can use this UDF. Open VBE (Alt+F11), then goto Insert - Module. Paste
this in:

Function CombineText(TextArray As Range, Divider As String)
For Each cell In TextArray
If CombineText = "" Then
CombineText = cell.Value
CombineText = CombineText & Divider & cell.Value
End If
Next cell
End Function

Now, back in your workbook, the formula is:

This should give you the some versatility in determining how many cells to
string together, as well as how you want the texts divided.

Gord Dibben


Your function does not make allowance for blank cells and will return extra
commas if the range contains blank cells.

Try this one which ignores blank cells.

Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange22(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

Gord Dibben MS Excel MVP

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