How to combine 2 strings and how to filter out all lines with certain charactors

[TammyTian]

Hello My problems are:

1) I have 2 column and both are string, I wanna combine them into another new column, like column 1: ABC and column 2: EDF and I wanna a new column ABCEDF. I use column 1 + column 2 which could give me the result however why column 1 AND column 2 doesn't work as they are string, "AND" shall be more reasonable than "+" right?

2) There may contain character "tax" in the column, may be start or end with it or just contain it. I wanna any record where start and end and contain it to be marked as "need further check". like:

"TAX" or "tax" or "Tax" or "tax 5%" or "VAT tax" shall be marked out but "taxi" shall not be marked. If formula doesn't help. how should I do?

[DataNath]

Hey @TammyTian, to your first point: AND is a boolean operator. For example, when you're filtering data and have multiple conditions, this checks that condition 1 AND condition 2 etc... are met. To simply ADD things together (including string concatenation), we just use +.

For your second point, if you want to check for 'tax' being ANYWHERE within the string in ANY format i.e. capitalisation, title case etc, then the best way is to use Contains(). However, to check for variations of caps then normalise both strings i.e. make them both all lower/uppercase. So you'd do something like:

Contains(Lowercase([Data_field]), 'tax')

[TammyTian]

Thanks Castor, it works. But when I change the key words to "VAT", the results involves lines like "renovation" where "vat" is part of the words, how can I eliminate those lines, keep records only contain "VAT" as a independent words stay.

[Qiu · Accepted solution]

@TammyTian 
Maybe we can use "Find and Replace" tool with a mapping file as another input.

[TammyTian]

Thanks Qiu, After several different tries I eventually adopted this way as the solution. I think it is the most efficient way I know so far for this problem.

[Qiu]

@TammyTian 
Glad to know it is helps.