Forum Discussion
Conditional Formatting won't work on data pasted from internet
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
Hi Dan,
Yes, please remove sensitive information and attach to your post.
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
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.
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