JMP Column with embedded LineFeeds?

[#1]

I have an excel spreadsheet that I loaded into JMP. One of the columns can have 0, 1, or 2 text string values stored in the column. When you look at the spreadsheet, the values appear on separate lines. In JMP, they appear scrunched together, with no apparent separator between the values. I need to break out each of the values, which are variable in length. I'm not sure how I can do this. Any suggestions?

[#2]

I am not sure what you mean, but if you see the strings on separate lines within a excel cell that may just represent a formatting setting in excel. JMP imports cell contents and not the formatting.

But if it is an import error you can try copy the table in excel and paste with labels (on Mac hold down option key and paste, probably similar in Windows) and compare with the imported table.

[#3]

the excel data is loaded from excel and exported back out to excel OK. In excel, a cell might look like this:

This is line 1
This is line 2
This is line 3

In JMP, it looks like this:

This is line1This is line2This is line3

when the JMP file is saved as XLS (after modifying some column values, the cell looks ok as 3 separate lines.

How are those line feeds being preserved? Is there a way I can parse out the JMP column to extract out the 3 values, so I can do some string manipulation?

Not sure it's an excel setting since the text strings are variable length.

[#4]

Ok, these excel cells probably contain manually inserted soft returns (soft line breaks).

For me these are preserved in JMP but it looks horrible since JMPs row-height is fixed. Lines 2 & 3 stretch below the row and thus blurring the content of the rows below instead of in-line as it did in your case. I have JMP 8. Do you have an older JMP version (5) that does not support unicode?

Forced soft returns in Excel (alt+enter) should be avoided. It is more fool-proof when transferring to other software to add whitespace between words and, if the layout is important, select wrap text in the cell format dialog.

[#5]

In JMP 8, I have to expand the width of the column to see all the content. The multiple lines do not appear as separate physical rows.

The EXCEL file was built by someoneelse, and I'm not sure if they could use some other delimiter like a semicolon.

With the current structure, is there anyway I can detect those soft linefeeds using JMP built in functions/string handling???

[#6 · Accepted solution]

I have the Mac version and regardless of column width I get tree lines within one row. The different appearance probably has to do with encoding definitions that differ among platforms.

I can interactively select and copy the invisible linefeed in a cell, and paste it into the find dialog (still invisible) and the replace all with something else.

The corresponding JSL code would be something like

For Each Row(
new = Substitute( Column( 1 )[], "\!n", "; " );
Column( 1 )[] = new;
)

[#7]

This worked!!!!!

THANK YOU VERY MUCH!!!!!!!

[#8]

In JMP 14 and above (and I beleive in JMP 13 as well) there is an even better way of handling these: Using Multiple Response option. THe Excel Import Wizard imports the data accurately, though the row span is too small to see all the values immediately. However if you increase the row span you'll see the other values as well.

Then you have to set the modeling type of that character column to "Multiple Response" and set the property "Modeling Response" to "" (empty) to handle the new line appropriately. Of yourse you could also use the above mentioned script and use ";" as a delimiter.

Hope that helps others having that problem. However, I'm not a big fan of using Excel in that way, as it complicates analysis. Having one value in each row makes it much more appropriate for analysis than wrapped cells.