This post describes how to split words in a cell range into a cell each using a custom function. I hope this picture explains it all:
The cell range is A1:A10 and contain words.
The array formula in C2:C27 contains an udf:
Select all the cells to be filled, C2:C27
Type the array formula SplitWords($A$1:$A$10) into the formula bar.
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
User defined function: Read Rick Rothstein's (MVP - Excel) comment!
Function SplitWords(rng As Range) As Variant()
Dim x As Variant, Wrds() As Variant, Cells_row As Long
Dim Cells_col As Long, Words As Long, y() As Variant
Wrds = rng.Value
For Cells_row = LBound(Wrds, 1) To UBound(Wrds, 1)
For Cells_col = LBound(Wrds, 2) To UBound(Wrds, 2)
x = Split(Wrds(Cells_row, Cells_col))
For Words = LBound(x) To UBound(x)
y(UBound(y)) = x(Words)
ReDim Preserve y(UBound(y) + 1)
SplitWords = Application.Transpose(y)