On Fri, 3 Aug 2012 23:34:54 -0700 (PDT), noname wrote:
hi,
Given a range say, "A1:E1", i need a formula to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns.
has anyone tried something like this?
Your question is not clear.
If you want to return a string that consists of the concatenation of the cells in your defined range, Excel does not have a built in function to do that unless you know the orientation and dimension of the string beforehand.
In your example, which is a horizontal string consisting of five elements, you could extend the following to the fifth element:
=INDEX($A$1:$E$1,1,1)&" "&INDEX($A$1:$E$1,1,2) & " " & INDEX($A$1:$E$1,1,3) ...
and there are other formulas that could work similarly, but would have the same limitations.
If a VBA solution is allowed, it is relatively simple.
Here is a User Defined Function that allows you to supply a range of any size, and includes an optional argument for a separator. If the argument is omitted, the routine will insert a <space between each element.
To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=MultiCellConcat(A1:E1)
in some cell.
===========================================
Option Explicit
Function MultiCellConcat(rg As Range, Optional Sep As String = " ") As String
Dim s As String
Dim c As Range
For Each c In rg
s = s & Sep & c
Next c
MultiCellConcat = Mid(s, 2)
End Function
================================================== ==