...

Split a text into multiple rows

 

Example

There are multiple lines of text in column D and column E in the Excel file book1.xlsx, the number of lines is the same, and one-to-one correspondence, for example, F corresponds to Fail.

Split column D and column E by line breaks and convert them into multiple rows. The result should be as follows:

Write SPL script:

A

1

=T("E:/work/book1.xlsx")

2

=A1.run(Grades=Grades.split("\n"),Comment=Comment.split("\n"))

3

=A2.news(Grades.len();Names,Class,Year,Grades(#):Grades,Comment(#):Comment)

4

=T("E:/work/book2.xlsx",A3)

A1 Read in book1.xlsx file data 

A2 Loop through each line of A1, and split Grades and Comment into text sequences by \n 

A3 Loop each row of A2 and convert this row into multiple rows. The number of rows is the number of members in the Grades sequence. In each row, take the original Names, Class, and Year columns, the #th member of the Grades sequence is the new column Grades, and the #th member of the Comment sequence is the new column Comment, where # represents the row number converted from this row. 

A4 Save A3 to the file book2.xlsx

 
 •  301 View  •