There are numerous ways to split text in Excel. In this article, we will see several methods for Excel split text by space formula. We have a sample dataset containing Names. Space is used as a delimiter here. Let’s see, how to split text by space using formulas in Excel.

In this post, we will see the use of **LEFT**, **FIND**, **SEARCH**, **RIGHT**, **TRIM**, **LEN**, **SUBSTITUTE**, **COLUMNS **functions and use a **VBA **code to split text by space.

## 1. Combining LEFT and FIND Formula to Split Text by Space in Excel

At first, we will extract the **First Name** which is the left portion of the name using the **LEFT** and **FIND** functions.

**Steps:**

- First, type the following formula in cell
**C5**.

`=LEFT(B5, FIND(" ",B5))`

- Now, press the
**ENTER**key.

Here, **FIND(” “,B5)** gives the output as **5**. Which are total letters including **Space **from the left side of the name. Then **=LEFT(B5, 5**) gives us the result as **Mary**.

- Finally, drag down to
**AutoFill**rest of the series.

**Read More: **How to Split First And Last Name in Excel

## 2. Splitting Text by Space Using Excel MID and FIND Functions

Now, we will split the middle name which is separated by space using **MID** and **FIND **functions.

**Steps:**

- First, type the following formula in cell
**D5**.

`=MID(B5,FIND(" ",B5),FIND(" ",B5,FIND(" ",B5)+1)-FIND(" ", B5))`

- After that, press the
**ENTER**key.

- Lastly, drag down to
**AutoFill**rest of the series.

So, what is happening here? **FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “, B5)** determines the number where the second space is. In this case, it is **10**. And, **=MID(B5,5,10)** gives the result as **Elizabeth**. **5** is the **start number**, and **10** means the** total number of characters**.

**Read More: **How to Split Text in Excel by Character

## 3. Joining Excel RIGHT and SEARCH Formula to Split Text

To split the Last Name, we will use the **RIGHT**, **LEN**, and **SEARCH** functions. Both the functions **FIND **and **SEARCH **have the same properties.

**Steps:**

- First, type the following formula in cell
**E5**.

`=RIGHT(B5,LEN(B5)-SEARCH(" ",B5,SEARCH(" ",B5,1)+1))`

- Now, press the
**ENTER**key.

- Finally, drag down to
**AutoFill**rest of the series.

Thatâ€™s it. Here, formula **SEARCH(” “,B5,SEARCH(” “,B5,1)+1) **gives us the output as **15 **which i the number of characters inctuding space of **Mary **and **Elizabeth**.**LEN(B5) **yields the result **20**. Which means,** LEN(B5)-SEARCH(” “,B5,SEARCH(” “,B5,1)+1)** basically gives output as** 5**(20 minus 15). Finally, **=RIGHT(B5,5)** gives the final result as **Smith**.

**Read More: **How to Split Text in Excel Using Formula

## 4. Splitting Text by Space with Formula Combining Excel Functions

In this method, we will use a combination function of **TRIM**, **SUBSTITUTE**, **COLUMNS**, **LEN**, and **REPT** functions to split texts by space.

**Steps:**

- Firstly, type the following formula in cell
**C5**.

`=TRIM(MID(SUBSTITUTE($B5,"",REPT(" ",LEN($B5))),(COLUMNS($B4:B4)-1)*LEN($B5)+1,LEN($B5)))`

- Now, press the
**ENTER**key.

- At this point, drag down to the right to
**AutoFill**the row series.

- At last, drag down to
**AutoFill**rest of the series.

For your information, links for each function are provided here. If the formula intrigues you about what is happening here. Please click the link and have a look at their uses and formula breakdowns.

## 5. Running VBA Macro to Split Text by Space

In our last method, we will use a **VBA** code to split texts by space.

**Steps:**

- First,
**right-click**on the sheet and go to**View Code**.

- After that, copy and paste the
**VBA**code below.

**VBA code:**

```
Sub SplitTextbyspace()
Dim Mydataset() As String, Count As Long, J As Variant
For Rnumber = 5 To 10
Â Â Â Â Mydataset = Split(Cells(Rnumber, 2), " ")
Â Â Â Â Newdest = 3
Â Â Â Â For Each J In Mydataset
Â Â Â Â Â Â Â Â Cells(Rnumber, Newdest) = J
Â Â Â Â Â Â Â Â Newdest = Newdest + 1
Â Â Â Â Next J
Next Rnumber
End Sub
```

- Finally, press the
**F5**or**play button**to run the code.

Here, **For Rnumber = 5 To 10** means our row number of the dataset and **Newdest=3** represents the first column where the text will split and follows on.

**Download Practice Workbook**

## Conclusion

Thatâ€™s all for the article. These are 5 different methods for Excel split text by space formula. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

## Related Articles

- How to Split Text by Number of Characters in Excel
- Split Text in Excel into Multiple Rows
- How to Split Text after a Certain Word in Excel
- Split String by Character in Excel
- Split String by Length in Excel
- How to Separate Two Words in Excel

**<< Go Back to Splitting Text |Â Split in Excel |Â Learn Excel**