Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Remove leading single quote from data in excel file

RitaB
7 - Meteor

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:

RitaB_0-1648145749414.png

RitaB_1-1648145784520.png

 

 

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:   

 

RitaB_2-1648145969059.png

 

Any suggestions for a tool or formula that would fix this?  Thanks!

4 REPLIES 4
atcodedog05
22 - Nova
22 - Nova

Hi @RitaB 

 

Can you provide an excel file with the issue.

atcodedog05
22 - Nova
22 - Nova

Hi @RitaB 

 

You can use TrimLeft() function like below

 

atcodedog05_0-1648146310162.png

 

Hope this helps : )

 

RitaB
7 - Meteor

@atcodedog05 thank you so much!

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @RitaB 

Cheers and have a nice day!

Labels