Split text cell into columns of words.

DRSteele · Mar 16, 2020

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 · Mar 16, 2020

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:

VBA Code:

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 · Mar 16, 2020

Thanks for the supplement, Smozgur!

Peter_SSs · Mar 18, 2020

DRSteele said:
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)))

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.
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 · Mar 18, 2020

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

Thanks, Peter!

Peter_SSs · Mar 18, 2020

DRSteele said:
Thanks, Peter!

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

smozgur said:
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

VBA Code:

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

smozgur · Mar 18, 2020

Peter_SSs said:
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 · Mar 18, 2020

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

Split text cell into columns of words.

DRSteele

Well-known Member

Excel Facts

smozgur

BatCoder

DRSteele

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

DRSteele

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

smozgur

BatCoder

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

Split text cell into columns of words.

Well-known Member

Excel Facts

BatCoder

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

BatCoder

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock