i have trouble running into this formula when i have slight different variations of data. The idea is to capture the brand in between square bracket but some contains " " inside the square brackets [] contains the brand name. As per the screenshots, you will see the variations of the data.
CodePudding user response：
I have entered the following in cell D2.
=index(ifna(
regexextract(A2:A4,"""(.*?)"""),
regexextract(A2:A4,"#.*?\[(.*?)\]")))
The formula extracts everything between the first occurrence of quotation marks "", if no match is found (IFNA) it extracts everything between the first pair of square brackets preceeded by #.
Update
=index(ifna(
regexextract(A2:A5,"(?:""|'')(.*?)(?:""|'')"),
regexextract(A2:A5,"#.*?\[(.*?)\]")))