Pulling Apart Cells

@Karen Mater: I think you must mean to the right, but quite so.

A formulaic approach, common on the internet, that works either with or without SPILL functionality, is:

=TRANSPOSE(FILTERXML("<outer><inner>"&SUBSTITUTE(A1," ","</inner><inner>")&"</inner></outer>","/outer/inner"))

Basically, it is looking for HTML tags, so you give it some. You will replace the spaces (to get the words separated) with the "inner" tags and place the "oiuter" tags at beginning and end to get the pretend data structure. Notice the first of each tag pair has just the tag inside its "<>" ( "<inner>" while the closing member of each pair has a "/" before the tag (" </inner> ": The reason that seems wrongly done inside the SUBSTITUTE() function is that you placed the starting one before it and therefore it needs the closing one for that, then the opening one for the next word. At the end you join the closing one of them and then the closing one for the whole string FILTERXML is looking at. This has now created an HTML string with the structure of "Outer" for the whole collection with "Inner" for each member of the collection. Obviously, it could be a lot more complicated for other uses, but they are not the use here so...

At the end, you tell FILTERXML what that structure is with what looks like any other path you ever had to use (except it's forward slashes, not backslashes). Since "Inner" is the bottom level, like a current directory if working with files, and "Outer" is the next level up, you put them in that order: "/Outer/Inner" and you DO need to use those doublequote characters in the formula.

It likes to come out taking up rows in a column, but usually one would like it to push off to the right. TRANSPOSE() takes care of that. This is a place where it is nice compared to the Text-To-Columns: if there is not enough room for all the words, you get the #SPILL! error rather than overwriting other data. If you use {CSE} entry, you have to select the range you want the output to be in and ought to be able to notice that it is not empty so you are protected that way. Although... {CSE} WILL overwrite if you do it anyway. {CSE} also lets you do less than needed. If you have nine words and only enter the array formula into three cells, you only get the first three words and there are no ill effects otherwise. More than needed? #N/A! errors, but they can be ignored or treated various ways.

VERY interestingly, because of the form of the value, you can choose which word to select, if perhaps you want the seventh word, not all of them. And it knows the word "last" so you needn't jump through hoops to calculate the last one like with a lot of functions. You DO have to add parentheses as if "last" were a function: "last()". You do that in the XPATH (the path-like part at the end that tells FILTERXML() how the data is structured:

"/outer/inner[2]"

(to pick the second word). I find its Table-like (referencing a Table's headers to be precise) constructionthe interesting part.

Another useful thing (usually useful) is that if you separate out a number, it is returned by this as a number, not as a string. So you can use it directly. Also, since that is so, if you have a larger formula around it that tests each element for "numberness" and uses the ones that pass, or the opposite, the test is quick and easy because no conversion needs made which means no allowance needs made for the non-number elements failing such tests and yielding errors.

It is seldom really explained, and the URL below doesn't do a marvelous job with that aspect of things either, but it IS a quick to read paragraph that has everything you need to quickly use the idea:

https://chandoo.org/wp/extract-words-with-filterxml/

To split as shown in the first option (without a macro), you need to have enough blank columns to the left of the column you are splitting, or it will overwrite the content already in that column.

albertan
Create another variable, say k2 to find the next word and change the code to:

Sub PullApart()
Dim Cell As Range
Dim k As Integer, k2 As Integer


For Each Cell In Selection
k = InStr(Cell, " ")
k2 = InStr(k + 1, Cell, " ")
If k Then
Cell.Offset(0, 1) = MID(Cell, k + 1, k2 - k)
Cell.Offset(0, 2) = Right(Cell, Len(Cell) - k2)
Cell = Left(Cell, k - 1)
End If
Next
End Sub

Nice technique on VBA. If I want split the 3 word cell into 3 columns, what would be the code in this case? Thank you so much