Array syntax is a powerful feature in Excel. Array syntax allows you to create a list within your formulas which makes them self-contained. Typing array syntax is difficult as you need to use a combination of quotation marks, commas or semi-colons plus braces (curly brackets).

We can create a custom function that will convert an existing list into array syntax. You can choose between creating a row-based list (horizontal) or a column-based list (vertical).

The idea is that you can then use paste values to capture the syntax and then you can copy and paste it into your formulas.

In array syntax, commas separate columns, and semi-colons separate rows.

In the image below we have the days of the week in column A.

The formula in cell D2 (image below) converts the list into array syntax for a horizontal list (across columns).

Formula in cell D2 is.

=LET(q,CHAR(34),"={"&q&TEXTJOIN(q&","&q,1,A1:A7)&q&"}")

The c variable represents double quotation marks. All the text is combined to create the array syntax.

The formula in cell D3 (image below) coverts the list into array syntax for a vertical list (down rows).

Formula in cell D3 is.

=LET(q,CHAR(34),"={"&q&TEXTJOIN(q&";"&q,1,A1:A7)&q&"}")

In the TEXTJOIN function the comma between the quotation marks has been replaced with a semi-colon.

Custom function

If you are new to custom functions then check out this blog post for an introduction.

The LAMBDA function that combines these two formulas is in cell D4 – image below.

The formula in cell D4 is.

=LAMBDA(rng,dir,
LET(
q,CHAR(34),
d,SWITCH(dir,"r",",","c",";"),
"={"&q&TEXTJOIN(q&d&q,1,rng)&q&"}"))(A1:A7,"c")

The range A1:A7 is passed to the rng argument. The “c” is passed to the dir argument.

The SWITCH function uses either the comma for a row range or a semi-colon for a column range.

The range name definition for the custom function that creates the syntax is shown below.

The custom function is used in the image below in cell D5.