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

[asifakhtar]

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 0) and then equal to sign and then the text. e.g 1=aaa.....

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

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


I have tried the following code but it starts from 1 instead of 0.
=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))

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(cel,";"&A2&";",s,SEQUENCE(,LEN(cel)-LEN(SUBSTITUTE(cel,";",""))-1),arr,REPLACE(LEFT(cel,FIND("^",SUBSTITUTE(cel,";","^",s+1))-1),1,FIND("^",SUBSTITUTE(cel,";","^",s)),""),TEXTJOIN(";",,s-1&"="&arr))