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

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?

Common Use Cases

Accepted answers

Qiu

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

1216-TammyTian.yxmd

All comments

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

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

1216-TammyTian.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703