Split text cell into columns of words.

[DRSteele]

DRSteele submitted a new Excel article:

Split text cell into columns of words. - Using Excel's new dynamic array formulas, we can easily 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...

Read more about this Excel article...

 

[smozgur]

Great article, @DRSteele!

I'd like to make a contribution to this concept.
I am impressed with Dynamic Array formulas every day. It even works with user defined functions.

I created this one liner as a function that simply returns an array with Split internal function:

Function splitText(rng As Range)
    splitText = Split(rng, " ")
End Function

Here is the result by using the same range as input:

SplitText.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Wall panels for interior wall.		Wall	panels	for	interior	wall.
2	This a test.		This	a	test.
3	This too is a test.		This	too	is	a	test.
4	This has too many spaces for the Word Number maxtrix, but is ok here.		This	has	too	many	spaces	for	the	Word	Number	maxtrix,	but	is	ok	here.
5	Notice how the punctation follows the word! Right?		Notice	how	the	punctation	follows	the	word!	Right?
6	…unless, of course, there is a space , like here .		…unless,	of	course,	there	is	a	space	,	like	here	.
Sheet1

Cell Formulas
Range	Formula
C1:G1,C6:M6,C5:J5,C4:P4,C3:G3,C2:E2	C1	=splitText(A1)
Dynamic array formulas.

 

[DRSteele]

Thanks for the supplement, Smozgur!

 

[Peter_SSs]

Using Excel's new dynamic array formulas, we can easily Split text cell into columns of words.

Hi Don

Two quite minor observations regarding your formula
=TRIM(MID(SUBSTITUTE($B13," ",REPT(" ",LEN($B13))),SEQUENCE(1,LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1,0)*LEN($B13)+1,LEN($B13)))

1. In relation to B13, you have used some absolute column references and some not. None are needed so for uniformity all the $ signs could be removed.
2. You could also make use of the STEP argument of SEQUENCE to remove the need for the multiplication.

=TRIM(MID(SUBSTITUTE(B13," ",REPT(" ",LEN(B13))),SEQUENCE(1,LEN(B13)-LEN(SUBSTITUTE(B13," ",""))+1,1,LEN(B13)),LEN(B13)))

 

[DRSteele]

Peter, your observations and suggestions are once again very valuable. I will change the article to reflect your improvements.

Thanks, Peter!

 

[Peter_SSs]

Thanks, Peter!

You're welcome. You are doing all the hard work.

I created this one liner as a function

Another very minor point. Since space is the default delimiter for Split, and you have assumed default settings for the other optional arguments, the one-liner could become

Function splitText(rng As Range)
    splitText = Split(rng)
End Function

 

[smozgur]

Since space is the default delimiter for Split

There is no single day that I don't learn something new! I probably used Split function 20+ years ago, and I just learned the space is the default delimiter in VBA.
Thanks, Peter!

 

[Peter_SSs]

There is no single day that I don't learn something new!