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!
Option Explicit 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 ReDim y(0) 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) Next Words Next Cells_col Next Cells_row SplitWords = Application.Transpose(y) End Function
Where to copy the code
- Click "Developer" tab on the ribbon
How to enable developer tab on the ribbon
- Click "Visual Basic" button
- Insert a new module
- Copy this udf example and paste it into new module
Download excel file
extract words from a range udf.xls
(Excel 97-2003 Workbook *.xls)