Cleaning Up Phone Number Lists

I have an old customer list maintained by my company's previous owner in excel format that mostly stored numbers ###-#### format, but also some in ###-###-#### format.
My new list has always been maintained with numbers in (###) ###-#### format.

I have merged these two lists in a new excel file have begun the tedious process of cleaning up duplicates and bad data.

I have used conditional formatting to highlight duplicate numbers in the appropriate columns (L, M, N, O) in this case. However, it does't find matches in the other formats or perhaps the number with the old area code. I know the area code change could lead to false positives, but they should be minimal as this is a small business with mostly local customers. So what I'd like to find out is a formula for conditional formatting that will compare the last 8 characters in each cell (###-####) so as to eliminate issues of missing or different area codes. I would like it to check all of the columns for duplicate numbers not just within each column or within each row individually.

Also separate but related if possible to highlight a different color any cells with data not in any of these formats or if not in (###) ###-#### format if it's too much for all the formats.

Thanks in advance.

To help tidy things up a bit if you only need the last 8 characters then you could use the RIGHT function.

But which one is the right one? Only kidding.

I read about it but couldn't figure out what else to put in the conditional formatting formula box.

Originally Posted by windy58

To help tidy things up a bit if you only need the last 8 characters then you could use the RIGHT function.

I was thinking you could use helper columns to reformat all the numbers using the RIGHT function into another column then copy & paste special values over your existing data then delete the helper columns.

Windy

I don't want to get rid of the rest of the data (name area codes) i just want the duplicate search to find duplicates without considering the area code so that I can look at them and see if its the same number with our cities old area code or an out of state one or what.

Thanks for your suggestions thus far.