Text To Columns: handle missing data correctly

[hogi]

☐ cool new feature
☐ could help many users!

☑ removes a „bug“

☐ nice to have

☐ nobody needs it

Text to Columns is a cool tool to split data from a single column to multiple columns - with a user define separator.

The tool has a neat feature - which (in manny cases) acts more like a bug:

If there  are consecutive separator characters, they are automatically treated like a single character (!)

Definitely, this makes sense for spaces. 

But let's assume that the data is from a data table. Then two consecutive separator characters indicate that there is an entry with a missing value in-between.
And Text to Column interprets it as a single character, this leads to shifted entries, like illustrated here:

https://community.jmp.com/t5/Discussions/Cols-utilities-how-to-let-lt-text-to-columns-gt-take-care-o...

My wish: please add a checkbox to enable/disable the "use multiple separators as a single one"  feature.

Out of historic reasons, one could enable the feature by default.

It's clear that there are workarounds like:

- write your own Text to Columns tool.

- add additional characters between adjacent separator characters.

- export the data set and use the txt import (which fortunately doesn't have this bug/feature)

- use Excel (which has the checkbox)

[hogi]

In the mean time I used Text to columns a lot with the current setting:
  I type several separator characters. and Jmp splist the text at each bunch of (!) characters

cool! This way of splitting should definitely stay as default option

What hurts at the moment: 
Advanced log doesn't record the action of Text to Columns!
So not just user-built add-ins have this issue:
Workflow Builder + AddIns 

--> would be great if this issue could be fixed with the next release of Jmp.

NB: concerning the original wish:
Recode has an option to split text - treating every single character as a separator
--> can be used as a workaround.

[twiindley]

How do I upvote this?  I have the same problem.  Need an option to NOT treat consecutive delimiters as a single delimiter. 

[dmusuwathi]

I got the same problem as well. Excel handles it correctly, but I haven't figured it out in JMP yet. 

Apple;red;sweet;fruit

Orange;;fruit

banana;yellow;;fruit

avocado;green;;

I want the data to be-

Apple      red       sweet     fruit
Orange                              fruit
banana   yellow                 fruit
avocado green

But they end up as

Apple      red       sweet     fruit
Orange   fruit
banana   yellow   fruit
avocado green

[hogi]

Question is what is "correctly"

After some while with Jmp I really learned to love the current functionality. It is sooo useful - so often.
e.g. https://community.jmp.com/t5/Discussions/Extract-all-desired-elements-from-a-string-to-enable-multip... 

Actually, I just used the Excel text-to-column functionality to correct import issues (if I chose the wrong separator).  (*)

Workaround here: use the correct separator

The topic gets more urgent if there additional applications besides (*). Which ones?

[hogi]

When you decide to optimize the function,

please also add a checkbox to toggle between the two modes:

1) just calculate (like now)

2) formula columns ->  great feature if new data is added!