Conditional Formatting won't work on data pasted from internet

[Dan Schwenk]

Hello,

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...

For example:

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!

[#2]

Hi Dan,

Copying data from Web usually adds some invisible characters to the text, you need to clean them. Please check

https://support.office.com/en-us/article/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41

https://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e...

[#3]

Hi Sergei,

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?

Thanks,
Dan

[#4]

Hi Dan,

Yes, please remove sensitive information and attach to your post.

[#5]

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.

Thanks!

Dan

[#6]

Dan,

The difference is in spaces between first and last name. In text from Web these are non-breaking spaces CHAR(160), when you type names manually you use "normal" space CHAR(32).

You may replace non-breaking spaces manually - copy any one of them, Ctrl+H, Paste it in Find what and space in Replace with.

Or use SUBSTITUTE to convert the text.

[#7]

Hi Sergei,

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. 

Thanks again for your help! 

Dan

[#8]

Dan, that's you decision, as for me replacing is faster

- double click on any cell with value from Web and select non-breaking space

- Ctrl+C

- Enter

- click triangle next to the top left of A1 to select entire sheet

- Ctrl+H

- Ctrl+V

- Tab

- Space

- Tab, Tab, Enter

and you have all your copied cells clean