Excel formula to manipulate cell text

Question

I have this text value in an excel cell:

,!Đ49,-,Đ50*,!Đ173,-,Đ174*,Đ197,-,!Đ198,-,Đ199*,Đ264,!Đ314,-,Đ315*,Đ330,

I need to manipulate it to reach the following result withotu VBA:

,Đ49,-,Đ50,Đ173,-,Đ174,Đ197,-,Đ199,Đ264,Đ314,-,Đ315,Đ330,

I tried many ways, like LAMBDA, and multiple substitutes, but I am failed.

The closes result I was able to get to it is using FILTERXML as this:

=TEXTJOIN(CHAR(10); 1;  "," & FILTERXML("<t><s>" &SUBSTITUTE(SUBSTITUTE(E3; "!"; "<r>"); "*"; "</r>") & "</s></t>"; "//r"))&","

resulting this:

,Đ49,-,Đ50,Đ173,-,Đ174,Đ198,-,Đ199,Đ314,-,Đ315,

PROBLEMS are compared to desired result:

,Đ49,-,Đ50,Đ173,-,Đ174,Đ197,-,Đ199,Đ264,Đ314,-,Đ315,Đ330,

• When 'series' contains more than 2 elements, first ignored, in this case: it gives: 198-199 instead of 197-199
• Since I am filtering parts between '!' and '*' the rest elements gets chopped of, in this case: missing: 264 and 330

Could anybody help me please with a concrete help? Very much appreciated!

Answer 1

This appears to work with your sample text:

=","&TEXTJOIN(","; TRUE; TEXTSPLIT(
LET(
   arr; TEXTSPLIT(E3; ",-,");
   TEXTJOIN(",-,"; TRUE; FILTER(arr; ISNUMBER(FIND(","; arr))))
); {"*,!";"*,";",!";","}))&","

It is dependent on the following conditions:

1. the text value always begins and ends with a comma (,)
2. asterisks only appear before a comma (*,)
3. exclamation points only appear after a comma (,!)

Cheers!