Excel: string manipulation, appending each occurance of the word with a number

I can’t find a way in Excel to append subsequent occurance of a word Summary in the string by adding the number of it’s subsequent appearence. For example in a string:

name=Summary,type=multi,required=1,sku=TESTSKU1,price=0.0000,default=1|name=Summary,type=multi,required=1,sku=TESTSKU2,price=0.0000,default=1|name=Summary,type=multi,required=1,sku=TESTSKU3,price=0.0000,default=1

result would be:

name=Summary1,type=multi,required=1,sku=TESTSKU1,price=0.0000,default=1|name=Summary2,type=multi,required=1,sku=TESTSKU2,price=0.0000,default=1|name=Summary3,type=multi,required=1,sku=TESTSKU3,price=0.0000,default=1

I have strings with N-count of summary appearances.

Thanks and I’d appreciate any help.

=SUBSTITUTE(A1, "Summary", "Summary"&COUNTIF($A$1:A1, "Summary"))

Chatgpt suggested the above formula but that only counts the occurance per cell/row and not inside of the cell.

>Solution :

If you have Excel 365 you can use this formula:

=LET(d,TEXTSPLIT(A1,"|"),
cnt,SEQUENCE(1,COLUMNS(d),1,1),
TEXTJOIN("|",TRUE,MAP(d,cnt,LAMBDA(x,a,SUBSTITUTE(x,"Summary","Summary" & a)))))

It splits the string by the pipe and then adds per section the number to Summary.