Text to columns adding a ' character, which is not allowing a join

[trickydigger]

Hi All, 

I'm parsing a string into several fields and then needing to join it with a separate file, but when I do text to columns it will add a ' as a prefix to every value (see left_output.xls) as and example. I tried to replace the character with a formula with a blank and that doesn't work. Some of the records join and some of them don't, it's very odd. Any help would be appreciated. 

In the unmatched left field you'll see that in the cell it appears to be fine but in the formula bar a ' appears as the prefix. I am really confused how this could be. 

[aatalai]

@trickydigger have you tried using the data cleanse tool to remove punctuation? Or do you need puncations to stay in your cells?

[trickydigger]

Removing punctuation removes the delimiter which is an underscore. I've used a cleanse tool after I parsed it out and before the join and that doesn't work. 

[alexnajm]

It doesn't look like this is an Alteryx issue but rather an .xls issue - I output to .xlsx and it did not output the apostrophe. Do you have the option to output to .xlsx here?