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

We have a sample dataset containing Names. Space is used as a delimiter here. Let’s split the text using formulas in Excel.

Method 1 – Combining LEFT and FIND Functions to Split Text by Space in Excel

We will extract the First Name.

Steps:

Use the following formula in cell C5.

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

Press Enter.

FIND(” “,B5) gives the output as 5 which are number of characters including the Space from the left side of the name. =LEFT(B5, 5) gives us the result as Mary.

Drag the Fill Handle down to AutoFill the rest of the series.

Method 2 – Splitting Text by Space Using Excel MID and FIND Functions

Let’s extract the middle name (surrounded by two spaces).

Steps:

Use the following formula in cell D5.

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

Press the Enter key.

Drag down the Fill Handle to AutoFill the rest of the series.

FIND(” “,B5,FIND(” “,B5)+1)-FIND(” “, B5) determines location of the second space. In this case, it is 10. =MID(B5,5,10) gives the result as Elizabeth. 5 is the start number, and 10 means the total number of characters.

Method 3 – Joining Excel RIGHT and SEARCH Functions to Split Text

Let’s get the Last Name.

Steps:

Use the following formula in cell E5.

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

Press the Enter key.

Drag down to AutoFill the rest of the series.

SEARCH(” “,B5,SEARCH(” “,B5,1)+1) gives us the output as 15 which is the number of characters including space of Mary and Elizabeth. LEN(B5) yields the result 20. LEN(B5)-SEARCH(” “,B5,SEARCH(” “,B5,1)+1) outputs as 5 (20 minus 15). =RIGHT(B5,5) gives the final result as Smith.

Method 4 – Splitting Text by Space with Formula Combining Excel Functions

Steps:

Use the following formula in cell C5.

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

Press the Enter key.

Drag to the right to AutoFill the row.

Drag down to AutoFill the rest of the series.

Method 5 – Running VBA Macro to Split Text by Space

Steps:

Right-click on the sheet name and go to View Code.

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