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 |
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 |
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. |
All times are GMT +1. The time now is 06:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com