Split and Expand Multiline Words in an Excel Cell into Multiple Rows
In the following Excel file, column D and column E contain multiline words. The values in both columns have same number of lines and a one-to-one relationship between lines. For instance, F in D2 corresponds to Fail in E2.
| A |
B |
C |
D |
E |
|
| 1 |
Names |
Class |
Year |
Grades |
Comment |
| 2 |
Name1 |
2nd |
2012 |
A |
Very Good, Needs Improvement |
| 3 |
Name2 |
4th |
2012 |
F |
Fail |
| 4 |
Name3 |
5th |
2012 |
A |
Very Good, Needs Improvement |
Task: Split each value in both column D and column E by line break and expand it into multiple rows. Below is the expected result:
| A |
B |
C |
D |
E |
|
| 1 |
Names |
Class |
Year |
Grades |
Comment |
| 2 |
Name1 |
2nd |
2012 |
A |
Very Good, Needs Improvement |
| 3 |
Name1 |
2nd |
2012 |
F |
Fail |
| 4 |
Name1 |
2nd |
2012 |
C |
Satisfactory |
| 5 |
Name2 |
4th |
2012 |
F |
Fail |
| 6 |
Name2 |
4th |
2012 |
F |
Fail |
| 7 |
Name3 |
5th |
2012 |
A |
Very Good, Needs Improvement |
| 8 |
Name3 |
5th |
2012 |
B |
Good, Needs Improvement |
| 9 |
Name3 |
5th |
2012 |
C |
Satisfactory |
Directions to get this done:
1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).
2. Write and execute script in esProc:
| A |
|
| 1 |
=file("data.xlsx").xlsimport@t() |
| 2 |
=A1.run(Grades=Grades.split("\n"),Comment=Comment.split("\n")) |
| 3 |
=A2.news(Grades.len();Names,Class,Year,Grades(#):Grades,Comment(#):Comment) |
| 4 |
=file("result.xlsx").xlsexport@t(A3) |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
