Convert cells of strings to array in Excel without VBA

Question

I've got a series of cells containing text, a cell repeating itself ('MYTITLE') :

MYFIXTITLE    
foo1    
bar1    
MYFIXTITLE    
foo2    
bar2    
MYFIXTITLE    
foo3    
bar3

And would like to convert it to 2 column-array like this :

first column : MYFIXTITLE for all cells
second column : foo(y) \n bar(y)

(y) being the line number of this array, \n being a new line.

Do you know how to do this in non VBA-Excel? Thanks

Gary's Student · Accepted Answer · 2016-09-13 17:46:16Z

2

With data in column A, in B1 enter:

=$A$1

and copy down and in C1 enter:

=INDEX(A:A,3*ROW()-1) & CHAR(10) & INDEX(A:A,3*ROW())

and copy down:

answered Sep 13, 2016 at 17:46

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1