Forum Discussion
Conditional Formatting won't work on data pasted from internet
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
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