#143 — Expand One Row into Multiple Rows after Splitting Text

#programming #beginners #tutorial #productivity

Problem description & analysis:

The following is a data table, in which columns D and E have multiple lines of text, the number of lines is the same, and such lines are in one-to-one correspondence. For example, F corresponds to Fail, as shown below:

Task: Now we want to split the values in column D, E by line break, and expand into multiple rows to make the result look like this:

Solution:

Use SPL XLL and enter the following code:

=spl("=E(?1).run(Grades=Grades.split(""\n""),Comment=Comment.split(""\n"")).news(Grades.len();Names,Class,Year,Grades(#):Grades,Comment(#):Comment)",A1:E4)

Code explanation: Loop through each row, split Grades and Comment into a string sequence by \n respectively, and then expand each row into multiple rows, the number of rows is the number of members of Grades sequence. In each new row, take the original Names, Class, and Year columns, the #th member of the Grades sequence is the Grades in new column, and the #th member of the Comment sequence is the Comment in new column, where # represents the row number expanded from original row.

Download esProc Desktop for FREE and simplify your workflow with SPL XLL!!! 🚀✨⬇️

SPL download address: esProc Desktop FREE Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

YouTube FREE courses: SPL Programming