ask for a formula to convert A2:C4 to E2

f01 f02 f03 expected result v11 v12 v13 insert into Sheet1 values("v11","v12","v13"),("v21","v22","v23"),("v31","v32","v33") v21 v22 v23 v31 v32 v33 ...

excel

office 365

Patrick2788

Oct 27, 2023

peiyezhu

I'm presuming you're looking for strict array notation (Removing the { and } )

=LET(
    str, ARRAYTOTEXT(A2:C4, 1),
    swap, SUBSTITUTE(str, ";", "),("),
    re_str, TEXTBEFORE(TEXTAFTER(swap, "{"), "}"),
    "(" & re_str & ")"
)

Try this:

Patrick2788

Silver Contributor

Oct 27, 2023

peiyezhu

I'm presuming you're looking for strict array notation (Removing the { and } )

=LET(
    str, ARRAYTOTEXT(A2:C4, 1),
    swap, SUBSTITUTE(str, ";", "),("),
    re_str, TEXTBEFORE(TEXTAFTER(swap, "{"), "}"),
    "(" & re_str & ")"
)

Try this:

peiyezhu

Bronze Contributor

Oct 28, 2023

Thanks.
Nice formula.

Patrick2788
Silver Contributor
Oct 28, 2023
You're welcome!

Forum Discussion

ask for a formula to convert A2:C4 to E2

Resources