• If you would like to post, please check out the MrExcel Message Board FAQ and register here.
    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.

Excel Version
  1. 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.']

MrExcel posts17_a.xlsx
BCDEFGHIJKLMNOPQRS
2Prior to Dynamic ArraysWord Number
3TEXTspaces in cellwords in cell12345
4Wall panels for interior wall.45Wallpanelsforinteriorwall.
5This a test.23Thisatest.  
6This too is a test.45Thistooisatest.
7This has too many spaces for the Word Number maxtrix of 5.1112Thishastoomanyspaces
8Notice how the punctation follows the word! Right?78Noticehowthepunctationfollows
9…unless, of course, there is a space , like here . 1112…unless,ofcourse,thereis
10
11Dynamic array
12TEXTspaces in cellwords in cell
13Wall panels for interior wall.45Wallpanelsforinteriorwall.
14This a test.23Thisatest.
15This too is a test.45Thistooisatest.
16This has too many spaces for the Word Number maxtrix, but is okay here.1314ThishastoomanyspacesfortheWordNumbermaxtrix,butisokayhere.
17Notice how the punctation follows the word! Right?78Noticehowthepunctationfollowstheword!Right?
18…unless, of course, there is a space , like here . 1112…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.
Author
DRSteele
Views
1,392
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top