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

Question

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.

Example.yxmd

dimension.xlsx

String_to_Parse.xlsx

left_output.xls

alexnajm · Answer

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?

trickydigger · Answer

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.

aatalai · Answer

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