Matching in excel

I’m stuck in this problem and I’m having a hard time to solve it. The automation is about breaking down an address into different categories. The matching and categorizing is already done, but there is one problem. Some data are not the expected output.

I think the reason why it’s giving me wrong output is that, there are some areas in different categories that has similar names so it’s having a hard time figuring out which one to put in the category.

Key reference are:

  1. Address to read can be lowercase, uppercase, case insensitive, separated by comma or blank space.
  2. Address is being match in the reference list (list of all the area in each category)
  3. If address doesn’t contain one of the category then just input “NA”

Hi, as per my understanding you are supposed to split the address into 3 and check if it exists in the reference page and if it does not mark it as NA? Is that correct?

yes, that’s right

Then I do have a way to do this, As I don’t have excel I am unable to run the file but how about using a string array to store the address by using the split(“,”) function and the checking if the length of the array is 3 and then adding that string array as a data row to check if it exists in the other data table. I think this method would be simple and give the right results.

This might work but what if the address is random, meaning it doesn’t follow the address format?