Forum Discussion

peiyezhu's avatar
peiyezhu
Bronze Contributor
Oct 27, 2023
Solved

ask for a formula to convert A2:C4 to E2

f01f02f03 expected result
v11v12v13 insert into Sheet1 values("v11","v12","v13"),("v21","v22","v23"),("v31","v32","v33")
v21v22v23  
v31v32v33  
    convert A2:C4 to E2
excel
office 365
  • Patrick2788's avatar
    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:

     

3 Replies

  • Patrick2788's avatar
    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:

     

Resources