Delete any substring that begins and ends with specific characters

Question

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

B1

ICT / Computer

B2

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

user_5632629 · Answer

try:

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

Delete any substring that begins and ends with specific characters

1 Answers