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**.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Split Text by Space Using Formula 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**.

### Method 1: Split Text by Space Using LEFT and FIND Functions

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 (6 Easy Ways)**

### Method 2: Split Text by Space Using 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 (5 Quick Methods)**

**Similar Readings**

**Split String by Character in Excel (6 suitable Ways)****Split String by Length in Excel (8 Ways)****How to Separate Two Words in Excel (6 Easy Ways)****Splitting Text in Excel Using Flash Fill**

### Method 3: Using RIGHT and SEARCH Functions

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 (5 Easy Ways)**

### Method 4: Split Text by Space Using Combined Formula

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.

**Read More: How to Separate Words in Excel Using Formula (Ultimate Guide)**

### Method 5: Using VBA 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.

**Read More: ****How to split text into multiple cells in Excel**

## Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, weâ€™ve attached a practice workbook where you may practice these methods.

## 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.