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

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, and the second is to use a formula using the TEXTSPLIT function. In this tutorial, we will learn both methods in detail.

Formula to Split Cells Text using the space

You need to follow 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 parentheses and hit enter to get the result in multiple cells horizontally.

=TEXTSPLIT(A1," ")

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

It will work if you have a text value with the three words in it.

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.

=TEXTSPLIT(A1,," ")

TEXTSPLIT is only available with OFFICE 365.

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).
   
2. Afterward, go to the Data → Data Tools → Text to Column. Click on it.
   
3. Now, by selecting “Delimited”, click on the “Next” button.
   
4. Next, tick marks the “Space” from delimiters and click on the “Next” button.
   
5. In the end, click on the finish button to split the 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 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 those using a space, you can create the formula for this. For this, you need to write three different formulas:

Formula 1: To Get the First Word:

=LEFT(A1, SEARCH(" ", A1))

It’s the first formula you need to enter in a cell to get the first word from the value. Then, it uses LEFT to get the text and SEARCH to locate the first space.

Formula 2: To get the Middle Word

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

In this formula, we are using MID and SEARCH to locate the first space. And then calculate the number of characters in the word

Formula 3: To get the Last Word

Finally, use the formula below to get the cell’s 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.