- Excel Version
- 365
If you have a cell containing a text string with spaces in it and you want to split it by spaces into columns, you can easily do so by using the Text-to-Columns functionality. Or you can use Power Query to clean up and split everything.
Or if you want to use formulas, this algorithm can help. Here we see the old way is a bit cumbersome and requires a set of columns with the anticipated number of words and hence requires user action to copy the formulas to the right. But the new dynamic function SEQUENCE really makes this a breeze, with a single-cell formula spilling the entire sentence out right-ways.
[Note that it keeps the punctuation marks attached to their nearest words, but users can attack that later. If punctation marks are preceeded by spaces, they too become a 'word.']
Or if you want to use formulas, this algorithm can help. Here we see the old way is a bit cumbersome and requires a set of columns with the anticipated number of words and hence requires user action to copy the formulas to the right. But the new dynamic function SEQUENCE really makes this a breeze, with a single-cell formula spilling the entire sentence out right-ways.
[Note that it keeps the punctuation marks attached to their nearest words, but users can attack that later. If punctation marks are preceeded by spaces, they too become a 'word.']
| MrExcel posts17_a.xlsx | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 2 | Prior to Dynamic Arrays | Word Number | ||||||||||||||||||
| 3 | TEXT | spaces in cell | words in cell | 1 | 2 | 3 | 4 | 5 | ||||||||||||
| 4 | Wall panels for interior wall. | 4 | 5 | Wall | panels | for | interior | wall. | ||||||||||||
| 5 | This a test. | 2 | 3 | This | a | test. | ||||||||||||||
| 6 | This too is a test. | 4 | 5 | This | too | is | a | test. | ||||||||||||
| 7 | This has too many spaces for the Word Number maxtrix of 5. | 11 | 12 | This | has | too | many | spaces | ||||||||||||
| 8 | Notice how the punctation follows the word! Right? | 7 | 8 | Notice | how | the | punctation | follows | ||||||||||||
| 9 | …unless, of course, there is a space , like here . | 11 | 12 | …unless, | of | course, | there | is | ||||||||||||
| 10 | ||||||||||||||||||||
| 11 | Dynamic array | |||||||||||||||||||
| 12 | TEXT | spaces in cell | words in cell | |||||||||||||||||
| 13 | Wall panels for interior wall. | 4 | 5 | Wall | panels | for | interior | wall. | ||||||||||||
| 14 | This a test. | 2 | 3 | This | a | test. | ||||||||||||||
| 15 | This too is a test. | 4 | 5 | This | too | is | a | test. | ||||||||||||
| 16 | This has too many spaces for the Word Number maxtrix, but is okay here. | 13 | 14 | This | has | too | many | spaces | for | the | Word | Number | maxtrix, | but | is | okay | here. | |||
| 17 | Notice how the punctation follows the word! Right? | 7 | 8 | Notice | how | the | punctation | follows | the | word! | Right? | |||||||||
| 18 | …unless, of course, there is a space , like here . | 11 | 12 | …unless, | of | course, | there | is | a | space | , | like | here | . | ||||||
ttc | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C4:C9,C13:C18 | C4 | =LEN(B4)-LEN(SUBSTITUTE(B4," ","")) |
| D4:D9,D13:D18 | D4 | =LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1 |
| F4:J9 | F4 | =TRIM(MID(SUBSTITUTE($B4," ",REPT(" ",LEN($B4))),(F$3-1)*LEN($B4)+1,LEN($B4))) |
| B13:B15,B17:B18 | B13 | =B4 |
| F13:J13,F18:Q18,F17:M17,F16:S16,F15:J15,F14:H14 | F13 | =TRIM(MID(SUBSTITUTE(B13," ",REPT(" ",LEN(B13))),SEQUENCE(1,LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1,1,LEN(B13)),LEN(B13))) |
| Dynamic array formulas. | ||
