Home > Blockchain >  Group text strings from a list in excel
Group text strings from a list in excel

Time:05-04

I am trying to make kws grouping using excel, but my formula is becoming gigantic. I was wondering if there is an easier way to do what I am trying to accomplish here.

I have a list like this below

enter image description here

And the expected results are as below

enter image description here

I have tried to use the formula:

=if(or(isnumber(search("query kw",A1,1),(isnumber(search("query 2",A1,1)),"Brand kws","Others"))

But this formula is becoming gigantic, with the number of queries, so it would be great to have a solution, where the search, would search the kws from a list, and I would only need to keep adding more words there instead.

Thanks for the help

Regards Gabriel

CodePudding user response:

One way could be:

enter image description here

Formula in E2:

=CONCAT(IFERROR(IF(SEARCH(" "&A$2:B$4&" "," "&D2&" "),A$1:B$1,""),""))

Note: When in Excel 2019 use Ctrl Shift Enter to confirm.

CodePudding user response:

Here is another alternative,

FORMULA_SOLUTION

• Formula used in cell E2

=INDEX($A$1:$B$1,SUM(ISNUMBER(SEARCH(" "&$A$2:$B$4&" "," "&$D2&" "))*(COLUMN($A$1:$B$1))))

Since its an array formula, based on your excel version need to press CTRL SHIFT ENTER , O365 & Excel 2021 Users don't need to press CTRL SHIFT ENTER

  • Related