formula to generate a string between two columns

[mountainman88]

I want to generate the following string: "Col x, Col y, Col x, Col z, Col aa, Col ab, Col ac, Col ad, Col ae" which is a list of consecutive excel columns prefixed with 'Col ' and separated by commas.

How can I generate this in a formula using only the first and last value? ie z and ae. I'd like this to work for any consecutive set of columns in excel. Bonus if the formula also works in google sheets.

The purpose is to generate the string for a google query formula, if there is an easier way please let me know.

Thanks!

 

[Umar568]

Thanks for asking.
You have to insert required text in rowA1 upto desired and then have to combine.
Here is formula...

=CONCATENATE("COL "&(A1),CONCATENATE(", COL "&(B1),CONCATENATE(", COL"&(C1),CONCATENATE(",COL"&(D1),CONCATENATE(",COL"&(E1),CONCATENATE(",COL"&(F1),CONCATENATE(",COL"&(G1),CONCATENATE(",COL"&(H1),CONCATENATE(",COL"&(I1),CONCATENATE(",COL"&(J1)))))))))))

Expected results....

COL A, COL X, COLY,COLZ,COLAA,COLAB,COLAI,COLJJ,COLKK,COLXX

Waiting for your response.

 

[Fluff]

This will work in Sheets, but not in Excel.

=ARRAYFORMULA("Col "&TEXTJOIN(", Col ",,SUBSTITUTE(ADDRESS(1,SEQUENCE(1,5,24),4),"1","")))