Remove leading single quote from data in excel file

[RitaB]

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!

[atcodedog05]

Hi @RitaB 

Can you provide an excel file with the issue.

[atcodedog05 · Accepted solution]

Hi @RitaB 

You can use TrimLeft() function like below

Hope this helps : )

[RitaB]

@atcodedog05 thank you so much!

[atcodedog05]

Happy to help : ) @RitaB 

Cheers and have a nice day!