Is there a way in VBA to have the SEARCH function only pick up cells that includes the "exact&q-CodePudding

I'm trying to get VBA / Excel to label cells as a specific category based on the specific text that the cell contains.

Currently I'm using the following:

''' =IF(ISNUMBER(SEARCH(""fee"",RC[-5]))),""fee"","N/A") '''

For the majority of the cells included in my regular dataset, I am able to categorise cells that contains the text "fee" as "Fee", however, there are cases where the cell could include word like "Coffee" where "fee" is also included.

In that case, these cells will also be categorised as "fee" as well which is not desirable.

Is there a way to avoid this by adding more condition / codes?

CodePudding user response：

You need to check if the cell starts/ends with Fee or if it's equal to fee.

My formula in column B is:

=IF(SUM(COUNTIF(A2;{"fee";"* fee";"fee *"}))>0;"Fee";"Other")

CodePudding user response：

If the contents is a multi-word string, you need to include delimiters.

In the formula below, I used space as the delimiter. Note that I added a space before and after the string being searched, and also used SUBSTITUTE to remove any punctuation. I only removed the dot, but, depending on your data, you may need to nest some SUBSTITUTE functions to also remove other punctuation (eg comma, exclamatdion points, etc)

=ISNUMBER(SEARCH(" fee "," " & SUBSTITUTE(G2,".","") & " "))