• 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.

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
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
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.
Cell Formulas
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)))
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
First release
Last update
0.00 star(s) 0 ratings

More Excel articles from DRSteele

Some videos you may like

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...