Adding Single Cell Array Constants

I would like to concatenate single cell array constants without taking help of other cells. A1={"A","B"} & B1={"1","2"}. As I could not access the array characters directly from the cell, I've named them as ARRAY1 & ARRAY2 & then used

Formula:

Please Login or Register  to view this content.

to get the desired result in C1. C1=A1 , B2

Now, the array constant {"A","B"} is having 1 row & 2 columns but if I want the INDEX formula (or any other formula) to repeat itself & add them for 'n' number of columns in Array i.e., {"A","B","C",........,"n"}, what formula should I use? The result will be like =A1 , B2 , C3 , D4 , E5 , F6 , ..... Nn

Is there any way out without naming the array constants?

VB script not allowed.

Hello excelformulae. Welcome to the forum.

Building the A,B,C ... 'n' and 1,2,3 ... 'n' arrays is no problem.

Building and concatenating such a variable length string and outputting to a single cell will require either TEXTJOIN or CONCAT function. Neither of these are available in Office 2007 as your profile shows. You would need Office 365.

Sad to say your only alternative for a single cell solution requires VBA.