I have data like this:
| COLUMN A | COLUMN B | COLUMN B MODIFIED |
| 812123005.01.00 | 0010.812123005.01.00 | 812123005.01.00 |
| 812200001.00.00 | 0010.812200001.00.00 | 812200001.00.00 |
| 001090598008401360 | 0010.905980084.01.36 | 905980084.01.36 |
| 011090598008401360 | 0110.905980084.01.36 | 905980084.01.36 |
What I'm trying to do is:
- I have [column B] data and I want to find only those that exists in [column A] too
- Most of the data in [column A] looks like row 1 and 2, so I modified the [column B] to be the same format (removed the first 5 characters)
- But there are some data in [column A] that has a different format (row 3 and 4)
- And I can't use the [column B modified] in that case
- Row 3 and 4 in [column B modified] is the same, but they're referencing different things because the first 4 characters in [column B] is different
So for row 1 and 2, I can use Join Tool between [column A] and [column B modified].
But for row 3 and 4, even if I modify the [column B] again to remove the dots, it still won't hit, because there's an extra 0 at the end in [column A] that is missing from [column B]. Is there a way to use Join Tool but also the feature String Contains at the same time? If I use the Filter tool, I have to manually put all the values with the different format from [column A] in the tool and I just thought there must be a more efficient way?
If possible, I don't want to modify [column A] at all.
