30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Split a Text using a Space Between in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

How to Split a Text using a Space Between in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To split text at each space, use =TEXTSPLIT(A1," ") in Excel 365 — it spills the words across columns automatically, however many there are. To stack them down rows instead, put the space in the third argument and leave the second empty: =TEXTSPLIT(A1,," ").

If you’re on Excel 2021 or earlier, TEXTSPLIT isn’t available, so use Data → Text to Columns → Delimited → Space, which does the same job but writes static values rather than a live formula.

Two things that trip people up: TEXTSPLIT overwrites whatever sits in the cells to its right, so leave those empty or you’ll get a #SPILL! error, and double spaces create empty results unless you add TRUE as the ignore-empty argument — =TEXTSPLIT(TRIM(A1)," ") is the safer version for messy data.

There are two quick ways to split a text value from a cell using the space between the words. One is to use the TEXT to COLUMN option, and the second is to use a formula with the TEXTSPLIT function. In this tutorial, we will learn both methods in detail.

Formula to Split Cell Text using the Space

You need to follow the below steps:

  1. First, enter the TEXTSPLIT function in a cell.
  2. After that, in the first argument, refer to the cell where you have the value.
  3. In the second argument, specify a space using double quotation marks.
  4. Ultimately, enter the closing parenthesis and hit Enter to get the result in multiple cells horizontally.
split-text-using-space-between
=TEXTSPLIT(A1," ")

The moment you hit enter, it splits the value into two cells, just like the below:

textsplit-to-split-values

It also works if you have a text value with three words in it.

text-value-with-three-words

And if you want to split the values into rows instead of multiple columns, you need to specify the space in the [row_deliminater] argument (row_delimiter) and leave the column delimiter argument empty.

split-values-in-rows
=TEXTSPLIT(A1,," ")

TEXTSPLIT is only available with OFFICE 365.

To be exact, TEXTSPLIT is available in Excel for Microsoft 365 (Windows and Mac), Excel for the web and Excel 2024. In Excel 2021 and older versions, use Text to Columns or the formulas in the sections below.

If the text has extra spaces (two spaces between words, or spaces at the start or end), TEXTSPLIT returns empty cells for them. Wrap the cell in TRIM to remove the extra spaces first:

=TEXTSPLIT(TRIM(A1)," ")

Split Cell Value with Text to Column using a Space

The next best way to do this is to use the TEXT to COLUMN option. With this option, you can split a single cell or do it for the entire column.

  1. Select the cell with the values you want to parse (split).
    split-cell-value-with-text-to-column
  2. Afterward, go to Data → Data Tools → Text to Columns. Click on it.
    go-to-text-to-column
  3. Now, select “Delimited” and click on the “Next” button.
    select-delimited
  4. Next, tick “Space” in the delimiters and click on the “Next” button.
    select-space-as-delimiter
  5. In the end, click on the “Finish” button to split the text.
    click-finish-to-split-text
  6. The moment you click on “Finish”, it will split values from a single cell into multiple cells according to the words that you have in it.
    split-values-into-multiple-cells

Two things to keep in mind: tick “Treat consecutive delimiters as one” in the same step if your text has double spaces, and Text to Columns overwrites the cells on the right of your data, so make sure they are empty. Also, the result doesn’t update when the original text changes, unlike a formula.

Split Text with Flash Fill

Flash Fill is another quick option. In the cell next to your text, type the first word just as you want it, then select the cell below and press Ctrl + E. Excel fills the rest of the column by following your example. Do the same in the next column for the second word, and so on. Like Text to Columns, Flash Fill gives you static values.

Split a Text using Spaces with FIND, LEFT, MID, and RIGHT

If you have a specific number of words in a cell and you need to split them using a space, you can create a formula for this. For three words, you need to write three different formulas:

split-text-using-spaces

Formula 1: To Get the First Word:

=LEFT(A1, SEARCH(" ", A1)-1)
left-to-get-first-word

It’s the first formula you need to enter in a cell to get the first word from the value. It uses SEARCH to locate the first space and LEFT to get the text before it. The -1 leaves out the space itself; without it, the result has a space at the end.

Formula 2: To Get the Middle Word

After that, you need to use the following formula to get the middle word.

mid-to-get-the-middle-word

In this formula, we are using SEARCH to locate the first space and MID to start from the character after it. Then, it calculates the number of characters in the word by subtracting the position of the first space from the position of the second space.

=MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1)-1)

Copy the formula exactly as it is here; it gives you the middle word without any spaces around it.

Formula 3: To Get the Last Word

Finally, use the formula below to get the cell’s last word.

right-to-get-the-last-word
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1,SEARCH(" ",A1)+1))

This formula uses SEARCH twice to get the second space in the cell and then uses RIGHT to get the word from the right side of the cell, which is the last word. Note that this formula needs at least three words; with only two, it returns the #VALUE! error.

Split Any Number of Words into Columns

If you don’t have TEXTSPLIT and the number of words changes from cell to cell, use the following formula. Enter it in B1 and drag it to the right as far as you need; each column gets the next word, and the extra columns stay blank.

=TRIM(MID(SUBSTITUTE(TRIM($A1)," ",REPT(" ",100)),(COLUMN(A1)-1)*100+1,100))

SUBSTITUTE replaces each space with 100 spaces, MID takes a 100-character piece for each word, and TRIM removes the extra spaces. COLUMN(A1) gives 1 in the first column, 2 in the next, and so on. It works for words of up to 100 characters.

Get the First or Last Word with TEXTBEFORE and TEXTAFTER

In Excel for Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER give you the first and the last word with a much shorter formula:

=TEXTBEFORE(A1," ")
=TEXTAFTER(A1," ",-1)

The first formula returns the text before the first space. In the second formula, -1 tells TEXTAFTER to use the last space, so it returns the last word, however many words the cell has.

Frequently Asked Questions

How do I count the words before splitting?

Use the following formula. It counts the spaces and adds 1. If the text can have extra spaces, use TRIM(A1) in place of A1.

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Why does TEXTSPLIT give me a #SPILL! error?

The cells where the result needs to go are not empty. TEXTSPLIT spills the words into the cells to the right (or below), so clear those cells and the result appears.

Can I split text by a comma and a space at the same time?

Yes. With TEXTSPLIT, give it more than one delimiter in curly brackets, like =TEXTSPLIT(A1,{” “,”,”}). With Text to Columns, tick both “Comma” and “Space”.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment