cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar

Text To Columns: handle missing data correctly

☐ 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.

hogi_0-1671111354594.png

 

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)

5 Comments
hogi
Level XII

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
Level I

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

dmusuwathi
Level I

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
Level XII

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
Level XII

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!