'Delete any substring that begins and ends with specific characters

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
regexif-statementgoogle-sheetssplitflatten


Solution 1:[1]

try:

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

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0

Related Questions