I tried to find a solution to my problem through these conversations but have had no luck...
I am pasting data from a website (contact info such as Names, City, State, etc.) into a spreadsheet as plain text and then add a conditional format to show duplicate names from a different column I already have in the same sheet. For some reason, the conditional format won't recognize duplicate values in either column, even though they are spelled the exact same and are both general or text format. If I re-type the same exact name in the cell that I pasted from online, it will then recognize it and add the conditional format...what am I missing here??? I have tried pasting the data from the spreadsheet online in every way possible (normal, plain text, unicode, html) but the conditional format won't recognize the names as duplicates...
Column A has 100 names (first and last in same cell). Column C has the pasted names (first and last in same cell) from a website. They are in the same format (general) and appear to be exactly the same but when I apply the conditional format - duplicate values to both Columns, nothing highlights (except the "name" header in Column C that appears twice in that column). When I re-type the name in column C, it will then recognize it as the same and then will highlight it. If I re-type the name in column A, nothing happens.
This will save me hours of time, as it would be helpful to know which names have already been added to my current database, so hopefully there is an easy solution here.
Thanks for the quick reply. I tried the CLEAN function and that did not work. I also checked the spelling, capitalizations and leading/following spaces, none of which are any different than the original column...any other thoughts? Would it help if I sent you the file or an example?
Attached is an example of what I was talking about. Column A is original data, Column C are example names pasted from the site (altered) and Column E is the same names from Column C except I typed them out individually (the flash fill feature popped up and I used that which actually worked pretty well).
As you can see, the name "Jackson Seagrave" is in all 3 columns but only highlights in Columns A and E.
I cannot distinguish ANY differences between C13 and A10/E13, hopefully you can.
That won't really work for me, as that will take more time than just reading through them.
The best solution I have found is to just clear the column on the left of the names and to start re-typing the names from the right and then using the Flash Fill to complete the rest of the names in the column.
I love excel and all of its capabilities but this one is sort of frustrating for something so behind the scenes.