• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Split text cell into columns of words.

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.']

MrExcel posts17_a.xlsx
BCDEFGHIJKLMNOPQRS
2Prior to Dynamic ArraysWord Number
3TEXTspaces in cellwords in cell12345
4Wall panels for interior wall.
4
5
Wall
panels
for
interior
wall.
5This a test.
2
3
This
a
test.
 
 
6This too is a test.
4
5
This
too
is
a
test.
7This has too many spaces for the Word Number maxtrix of 5.
11
12
This
has
too
many
spaces
8Notice 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
11Dynamic array
12TEXTspaces in cellwords in cell
13
Wall panels for interior wall.
4
5
Wall
panelsforinteriorwall.
14
This a test.
2
3
This
atest.
15
This too is a test.
4
5
This
tooisatest.
16This has too many spaces for the Word Number maxtrix, but is okay here.
13
14
This
hastoomanyspacesfortheWordNumbermaxtrix,butisokayhere.
17
Notice how the punctation follows the word! Right?
7
8
Notice
howthepunctationfollowstheword!Right?
18
…unless, of course, there is a space , like here .
11
12
…unless,
ofcourse,thereisaspace,likehere.
ttc
Cell Formulas
RangeFormula
C4:C9,C13:C18C4=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
D4:D9,D13:D18D4=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1
F4:J9F4=TRIM(MID(SUBSTITUTE($B4," ",REPT(" ",LEN($B4))),(F$3-1)*LEN($B4)+1,LEN($B4)))
B13:B15,B17:B18B13=B4
F13:J13,F18:Q18,F17:M17,F16:S16,F15:J15,F14:H14F13=TRIM(MID(SUBSTITUTE(B13," ",REPT(" ",LEN(B13))),SEQUENCE(1,LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1,1,LEN(B13)),LEN(B13)))
Dynamic array formulas.
Excel Version
365
Author
DRSteele
Views
644
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

Some videos you may like

This Week's Hot Topics

Top