Problem description & analysis:
In the following table, column A is the category, and column B includes one or multiple lines of text where the line break is the separator.
A | B | |
1 | Account Number | Interaction |
2 | 1 | Jan 1,2023 - Hello. |
3 | 2 | Jan 2, 2023 - Good morning. |
4 | 3 | Jan 1,2023 - Good night. |
The task is to expand each multi-line cell under column B into multiple cells and copy the column A value.
A | B | |
1 | Account Number | Interaction |
2 | 1 | Jan 1,2023 - Hello. |
3 | 2 | Jan 2, 2023 - Good morning. |
4 | 2 | Jan 3, 2023 - Good night. |
5 | 2 | Jan 4, 20 |
6 | 2 | Jan 5, 2023 - Good night. |
7 | 2 | Jan 6, 2023 - Good afternoon. |
8 | 3 | Jan 1,2023 - Good night. |
9 | 3 | Jan 2, 2023 - Good afternoon. |
Solution:
Use SPL XLL to do this:
=spl("=?.news@q(~2.import@si();[get(1)(1),~])",A2:B4)
Explanation:
news@q function generates a new sequence by computing members of an existing sequence; ~2 is the 2ndmember of the current variable; import@si parses a string into a sequence of single-line strings according to the carriage return; get() function returns the loop variable according to the layer number during a multilayer loop.
Please free to download SPL XLL and explore the data processing journey on your own⬇️
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso