I am at a loss for how to fix this:
The excel file I receive will have a handful of cells with a leading single quote - so instead of John Doe, it will appear as 'John Doe. When I try to find and replace the single quote, Excel says it can't find anything:
The file is not protected. Even if I just do "find" and not "find and replace" it cannot find the single quote. The format in Excel is general, but even changing it to text doesn't help.
Normally I wouldn't care, but when I run the cross tab, it thinks 'John Doe and John Doe are not the same, and puts them on separate rows, when I need them to be consolidated. I tried using Alteryx data cleansing and removing punctuation, but that did not change the results. It also removed the parentheses in other cells, which I did not want. Here's an example of the cross tab query where this is occurring:
Any suggestions for a tool or formula that would fix this? Thanks!
Solved! Go to Solution.
