How to Split Text by Space with Formula in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Split Text by Space Formula


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))

Excel Split Text by Space Formula LEFT FIND

  • Now, press the ENTER key.

Excel Split Text by Space Formula LEFT FIND

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))

Excel Split Text by Space Formula MID FIND

  • After that, press the ENTER key.

Excel Split Text by Space Formula LEFT FIND RIGHT SEARCH

  • Lastly, drag down to AutoFill rest of the series.

Excel Split Text by Space Formula LEFT FIND RIGHT SEARCH

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))

Excel Split Text by Space Formula RIGHT SEARCH

  • Now, press the ENTER key.

Excel Split Text by Space Formula LEFT FIND RIGHT SEARCH

  • 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)))

Excel Split Text by Space Formula TRIM COLUMNS SUBSTITUTE

  • Now, press the ENTER key.

Excel Split Text by Space Formula TRIM COLUMNS SUBSTITUTE

  • 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

Excel Split Text by Space Using VBA

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo