Conditional formatting in Excel

I copied over a list of names from a webpage and pasted the data into a column in Excel. I always use "Paste Special" > "Match Destination formatting". Works great and the data is all there etc. Th...

excel

Riny_van_Eekelen
Jan 22, 2021
Brewster56 Most likely, the texts you copied from the web contains "white space" that isn't empty. These can be trailing spaces or other hidden characters.
For a computer "Name" is not equal to "Name " (i.e. "Name" plus three spaces) even though they may look the same in your Excel document. Locate two cell in the data you copied from the web that look the same to you. Let's say A2 and A10 seem to be duplicates. Type the following formula somewhere:
=A2=A10
If the result is FALSE, they ar NOT duplicates and conditional formatting will not highlight them.
You can try functions like TRIM, CLEAN or SUBSTITUTE to clean-up the data. If this does not resolve the issue, perhaps you can upload a copy of the workbook (as long as it doesn't contain any private and confidential information).

Riny_van_Eekelen

Platinum Contributor

Jan 23, 2021

Brewster56 Good luck! Come back here if you need more help.

PS: Just after posting my response here, I replied to another question dealing with white-space. I added a workbook with a simple "tool" that helps identifying all the characters in your text. It uses one of the latest dynamic array functions (SEQUENCE).

https://techcommunity.microsoft.com/t5/excel/used-xlookup-and-came-back-with-result-n-a-although-text-fields/m-p/2089096/highlight/false#M86885