Stephen_IV
Well-known Member
- Joined
- Mar 17, 2003
- Messages
- 1,171
- Office Version
- 365
- 2019
- Platform
- Windows
Good morning, the formula below is doing exactly what I need, except, How do I make it spill?? Any help would be appreciated.
Dynamic Array Split Schools.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | TWINS , ASTROS , BOSTON | TWINS | ASTROS | BOSTON | ||
2 | DODGERS , BOSTON | DODGERS | DODGERS | BOSTON | ||
3 | DODGERS , BOSTON | DODGERS | DODGERS | BOSTON | ||
4 | DODGERS , BOSTON | DODGERS | DODGERS | BOSTON | ||
5 | ROYALS , RANGERS | ROYALS | ROYALS | RANGERS | ||
6 | TWINS , ASTROS , BOSTON | TWINS | ASTROS | BOSTON | ||
7 | DODGERS , BOSTON | DODGERS | DODGERS | BOSTON | ||
8 | ANGELS , YANKEES | ANGELS | ANGELS | YANKEES | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1:D8 | B1 | =LET( SplitArray, TEXTSPLIT(A1, ","), WordCount, COUNTA(SplitArray), IF( WordCount = 2, CHOOSE({1,2,3}, INDEX(SplitArray, 1), INDEX(SplitArray, 1), INDEX(SplitArray, 2)), IF( WordCount = 3, CHOOSE({1,2,3}, INDEX(SplitArray, 1), INDEX(SplitArray, 2), INDEX(SplitArray, 3)), {"","",""} ) ) ) |
Dynamic array formulas. |