Subscribe Bookmark RSS Feed

JMP Column with embedded LineFeeds?

whom

Community Trekker

Joined:

Jun 23, 2011

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?
1 ACCEPTED SOLUTION

Accepted Solutions
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;


)


6 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jun 23, 2011

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???
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;


)


whom

Community Trekker

Joined:

Jun 23, 2011

This worked!!!!!

THANK YOU VERY MUCH!!!!!!!