How can I split the text by semicolon in Column A and and make key/value in Excel?-CodePudding

I have an Excel sheet with 500 values in column A. I am looking for a formula to split the text in column A by semicolon and then append index(starting from 1) and then equal to sign and then the text. e.g 1=aaa.....

Text in Column A: aaa.;bbb.;ccc.

Desired Output: 1=aaa.;2=bbb.;3=ccc.

I am looking for a formula like the following in JavaScript:

console.log("aaa.;bbb.;ccc.".split(";").map((element, index) => (index 1) '=' element.trim()).join(';'));

CodePudding user response：

This will work in all versions of 365:

=LET(cel,A1,arr,TRIM(MID(SUBSTITUTE(cel,";",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(cel)-LEN(SUBSTITUTE(cel,";","")) 1))-1)*999 1,999)),TEXTJOIN(";",,SEQUENCE(COUNTA(arr))&"="&arr))

If using a PC then we can use FILTERXML:

=LET(cel,A1,arr,FILTERXML("<a><b>"&SUBSTITUTE(cel,";","</b><b>")&"</b></a>","//b"),TEXTJOIN(";",,SEQUENCE(COUNTA(arr))&"="&arr))

Also there is a new function, currently in beta that we can use called TEXTSPLIT:

LET(cel,A1,arr,TEXTSPLIT(cel,";"),TEXTJOIN(";",,SEQUENCE(COUNTA(arr))&"="&arr))