Challenge #2: Preparing Delimited Data

[santiesteban]

For some reason the " are not deleted from the text with the text to columns tool. Had to use a formula

[EmanueleE]

My solution!

Spoiler

[Joren]

~Joren

[Diptesh]

Done!

Regards,

Diptesh

[asabau]

Here it is!

Cheers!

[TeePee]

Spoiler

This is my first weekly challenge.  I managed to replicate the output (my solution attached) but I have a question about the Text to Columns tool.  Hope someone can help.

At first, I specified "split to 3 columns" and "skip empty fields", but even though I included single quotes as one of my delimiters, the last column retained the single quotes:

3 columns leaves in the single quotes3 columns leaves in the single quotes

So I thought perhaps I hadn't specified enough columns, upped it to 4 and tested the workflow.  Now this time the single quotes went away but then I had an additional empty field, even though I still ticked "skip empty fields":

4 columns removes single quotes and adds an empty column, even though I chose to skip empty fields4 columns removes single quotes and adds an empty column, even though I chose to skip empty fields

So, I thought I understood this tool but I can't understand what's going on.  Am I missing something?  I used the Select tool to deselect "Field_14" and get to the solution anyway, but I am curious as to what's happening.  

Thanks in advance for any replies.

My solution and a question.  Hope someone can help.  TIA. 

[mceleavey]

The reason it's leaving the quotes in when you specify three columns is that the condition has been met by that point, with the remaining text being left in the final column (as you can see in the drop-down option "Extra Columns"). This means the quoted text is simply untreated.

When you include four, it then applies the split to the text which was remaining in the first instance, that of three columns, and duly removes the quotes.

Hope this helps.

[TeePee]

Thanks for the reply.  Yes, that makes perfect sense, thank you!

Do you also have any idea why the last blank column is not removed when I've selected "Skip Blank Fields"?  Thanks again.

[mceleavey]

Ah, I see the confusion. This is not what you think it is :)

This check box allows you to skip empty fields when applying a delimiter that would otherwise be impacted by this, such as a space or tab delimiter. So this box relates to the source not the output.

Hope this clears it up!

[TeePee]

Aha!  Thank you so much :)