Problem with splitting up text in a column
I'm trying to convert the range from A1 to A5 to the range in C1 to D5 with a formula. I tried using a combination TEXTSPLIT and Index but I'm not getting anywhere. A day of googling and AI doesn't seem to help either.
The real data has thousands of rows, and sometimes will have 6 or 7 "pairs", and sometimes have no data (like in A3).
| + | A | B | C | D |
|---|---|---|---|---|
| 1 | Hello;123 | Hello | 123 | |
| 2 | Goodbye;234;Hello;345 | Goodbye | 234 | |
| 3 | Hello | 345 | ||
| 4 | Hello;456;Hello;678 | Hello | 456 | |
| 5 | Hello | 678 |
Table formatting brought to you by ExcelToReddit