Author: Oscar Cronquist Article last updated on July 17, 2022

Extract strings from a cell range

This post describes how to split words in a cell range into a cell each using a custom function and an Excel 365 formula.

1. Split words in a cell range into a cell each [UDF]

The cell range is A1:A10 and contains values.

The array formula in C2:C27 contains an udf:

  1. Select all the cells to be filled, C2:C27
  2. Type the array formula SplitWords($A$1:$A$10) into the formula bar.

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. 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
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

  1. Press with left mouse button on "Developer" tab on the ribbon
    How to enable developer tab on the ribbon
  2. Press with left mouse button on "Visual Basic" button
  3. Insert a new module
  4. Copy this udf example and paste it into new module

2. Split words in a cell range into a cell each - Excel 365

This formula works only in Excel 365, it extracts strings between spaces in a cell range and returns each string to a cell.

Excel 365 formula in cell C2:

=TEXTSPLIT(TEXTJOIN(" ", TRUE, A1:A10),," ",TRUE)

Explaining formula

Step 1 - Merge cell values

The TEXTJOIN function combines text strings from multiple cell ranges.

Function syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(" ", TRUE, A1:A10)

becomes

TEXTJOIN(" ", TRUE, {"Cessna 150"; "Cessna 172 Skyhawk"; "Cessna T-37 Tweet"; "Piper PA-18 Super Cub"; "Piper Continental C-90-8F"; "Piper Lycoming O-320"; "Beechcraft 390"; "Beechcraft 400"; "Beechcraft 1900"; "Beechcraft 2000"})

and returns

"Cessna 150 Cessna 172 Skyhawk Cessna T-37 Tweet Piper PA-18 Super Cub Piper Continental C-90-8F Piper Lycoming O-320 Beechcraft 390 Beechcraft 400 Beechcraft 1900 Beechcraft 2000".

Step 2 - Split text based on a space character as a delimiting character

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(TEXTJOIN(" ", TRUE, A1:A10),," ",TRUE)

becomes

TEXTSPLIT("Cessna 150 Cessna 172 Skyhawk Cessna T-37 Tweet Piper PA-18 Super Cub Piper Continental C-90-8F Piper Lycoming O-320 Beechcraft 390 Beechcraft 400 Beechcraft 1900 Beechcraft 2000",," ",TRUE)

and returns

{"Cessna";"150";"Cessna";"172";"Skyhawk";"Cessna";"T-37";"Tweet";"Piper";"PA-18";"Super";"Cub";"Piper";"Continental";"C-90-8F";"Piper";"Lycoming";"O-320";"Beechcraft";"390";"Beechcraft";"400";"Beechcraft";"1900";"Beechcraft";"2000"}