Delete any substring that begins and ends with specific characters-CodePudding

I have strings that looks like this in google sheets:

In A1

,information-technology#ICT / Computer

In A2

,agriculture#Agriculture / Agro-Allied,ngo#NGO/Non-Profit,project-management#Project Management

I need to remove everything that begins with , and ends with # including these two characters

If possible the output to be separated by a comma like so

ICT / Computer

Agriculture / Agro-Allied, NGO/Non-Profit, Project Management

CodePudding user response：

try:

=ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(
 TRANSPOSE(IF(IFERROR(SPLIT(A1:A3, "#"))<>"", 
 REGEXREPLACE(SPLIT(REGEXREPLACE(A1:A3, "(#)", "$1♥"), 
 "♥"), "(,. )", )&",", )),,9^9))), "^, |,$", ))