Author: Oscar Cronquist Article last updated on December 31, 2022

Extract strings from a cell range

This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of cells with each string in its own cell. The first part shows how to do this using a user defined function (udf) and the second part an Excel 365 formula.

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

This example demonstrates a user defined function (udf) that splits strings in  agiven cell range to a cell each. The image above shows random values in cell range A1:A10.

Cell range C2:C27 contains an array formula, here is how to enter this udf as an array formula:

  1. Select all the cells to be filled, C2:C27
  2. Type the array formula SplitWords($A$1:$A$10) in the formula bar.
  3. Press and hold Ctrl + Shift simultaneously.
  4. Press Enter once.
  5. Release all keys.

The formula now looks like this: {=SplitWords($A$1:$A$10)}

Don't enter these curly brackets yourself, they appear automatically.

Note, Excel 365 subscribers can enter this formula as a regular formula.

User defined function: Read Rick Rothstein's (MVP - Excel) comment!

'Name user defined function and declare parameters
Function SplitWords(rng As Range) As Variant()

'Dimension variables and their data types
Dim x As Variant, Wrds() As Variant, Cells_row As Long
Dim Cells_col As Long, Words As Long, y() As Variant

'Redimension variable y
ReDim y(0)

'Save values in given cell range to variable Wrds
Wrds = rng.Value

  'Iterate through rows in array variable Wrds
  For Cells_row = LBound(Wrds, 1) To UBound(Wrds, 1)

    'Iterate through columns in array variable Wrds
    For Cells_col = LBound(Wrds, 2) To UBound(Wrds, 2)

      'Split text string in given array container to a new array variable named x. The Split function returns an array of substrings based on a delimiting character and a given string .
      x = Split(Wrds(Cells_row, Cells_col))

      'Iterate through values in array variable x
      For Words = LBound(x) To UBound(x)

        'Save value to last array contain in variable y
        y(UBound(y)) = x(Words)

        'Add a new array container
        ReDim Preserve y(UBound(y) + 1)

      'Continue with the next value in array variable Words
      Next Words

    'Continue with the next column
    Next Cells_col

  'Continue with the next row
  Next Cells_row

'Rearrange (transpose) values in variable y and return those values to the worksheet.
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

Get the Excel file


extract-words-from-a-range-udf.xls

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"}