Transforming data in column into a linear text string

[mocha99]

Hi all,

my data in column A is like

cat
dog
apple

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
Else
CombineText = CombineText & Divider & cell.Value
End If
Next cell
End Function
'==============

Now, back in your workbook, the formula is:
=CombineText(A2:A4,",")

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]

Luke

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