Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transforming data in column into a linear text string
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transforming data in column into a linear text string
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "mocha99" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transforming data in column into a linear text string
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 On Tue, 11 Aug 2009 10:25:02 -0700, Luke M wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose a text string while copying adjacent column data to new | Excel Worksheet Functions | |||
averaging range across sheets after transforming text to value | Excel Worksheet Functions | |||
Convert column data to semicolon delimited text string | Excel Worksheet Functions | |||
Transforming a bitmap to text for integrating in Excell | Excel Discussion (Misc queries) | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) |