Create CSV in CELL from range of text values - An array formula perhaps (no vba)?

[marcus76]

Hi,

Grateful for some pointers on how i might create a csv in a cell (A1) from an adjacent range of values (B1:B6) - formulas only, no macros. There will be spaces at the end of the range, not in the middle. e:g


[TABLE="width: 246"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]csv here[/TD]
[TD]apples[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]bananas[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]pineapples[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]strawberries[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any assistance.

Rgds

Marcus

 

[TheSilkCode]

Hi marcus,

So obviously you could simply use CONCATENATE, listing out each cell with a comma inbetween:

=CONCATENATE(B1,",",B2,",",B3,",",B4)

but that can be a pain to type out, and if you don't know the number of rows you need to concatenate then its a problem... unfortunately I cant think of a good solution using purely Excel formulas..

However this is the PERFECT time to use a User Defined Function (UDF)- I know you said no macros, but if your concern is just you don't want some big clunky macro running and breaking, don't worry it wont be that- insert the following code into a module in the workbook:

Public Function RangeToCSV(targetRange As Range, Optional delimeter As String = ",") As String
Dim rowCounter As Long
Dim colCounter As Long
Dim targetArr() As Variant
Dim CSVStr As String
targetArr = targetRange
For rowCounter = 1 To UBound(targetArr, 1)
    For colCounter = 1 To UBound(targetArr, 2)
        CSVStr = CSVStr & IIf(CSVStr <> "", delimeter, "") & CStr(targetArr(rowCounter, colCounter))
    Next
Next
RangeToCSV = CSVStr
End Function

Now type '=RangeToCSV' into a cell (you should see excel start to autofill it) and select the range as the first parameter, in your example =RangeToCSV(B1:B4). You can even add a second parameter to specify the delimiter, but if you leave this blank it will use a comma as the default. I tested it and it works beautifully, hopefully this is a good solution for you.

Regards,
TheSilkCode

 

[marcus76]

Hi marcus,

So obviously you could simply use CONCATENATE, listing out each cell with a comma inbetween:

=CONCATENATE(B1,",",B2,",",B3,",",B4)

but that can be a pain to type out, and if you don't know the number of rows you need to concatenate then its a problem... unfortunately I cant think of a good solution using purely Excel formulas..

However this is the PERFECT time to use a User Defined Function (UDF)- I know you said no macros, but if your concern is just you don't want some big clunky macro running and breaking, don't worry it wont be that- insert the following code into a module in the workbook:

Public Function RangeToCSV(targetRange As Range, Optional delimeter As String = ",") As String
Dim rowCounter As Long
Dim colCounter As Long
Dim targetArr() As Variant
Dim CSVStr As String
targetArr = targetRange
For rowCounter = 1 To UBound(targetArr, 1)
    For colCounter = 1 To UBound(targetArr, 2)
        CSVStr = CSVStr & IIf(CSVStr <> "", delimeter, "") & CStr(targetArr(rowCounter, colCounter))
    Next
Next
RangeToCSV = CSVStr
End Function

Now type '=RangeToCSV' into a cell (you should see excel start to autofill it) and select the range as the first parameter, in your example =RangeToCSV(B1:B4). You can even add a second parameter to specify the delimiter, but if you leave this blank it will use a comma as the default. I tested it and it works beautifully, hopefully this is a good solution for you.

Regards,
TheSilkCode

WOW, love it! thanks very much, perfect indeed.

To take this a step further / or back, now i see what's doable with this user defined function, would it be possible to specify a range and cycle through each value, i.e. range is B1:B6, in A1 through A6 i'm cycling through the range of values, A5 is back at the start of the range and so on.

e.g



[TABLE="class: cms_table, width: 246"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apples[/TD]
[TD]apples[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bananas[/TD]
[TD]bananas[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]pineapples[/TD]
[TD]pineapples[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]strawberries[/TD]
[TD]strawberries[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]apples[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]bananas[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TheSilkCode]

Pretty much anything is possible- pull up the VBA editor and start playing around with your own UDFs, when you hit a impasse, post the code here.

Good luck!
TheSilkCode