How to combine 2 strings and how to filter out all lines with certain charactors
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TammyTian
Maybe we can use "Find and Replace" tool with a mapping file as another input.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TammyTian
Glad to know it is helps.
