How to Split Text String by Space, Comma & More – Excel and Google Sheets

This tutorial will demonstrate how to split text strings using a space, comma and more in Excel and Google Sheets.

Split Text String with Text to Columns

You can split a text string with the Text to Columns feature in Excel.

Select the text you wish to split.

In the Ribbon, select Data>Text to Columns.

Keep the option “Delimited” selected and click Next.

Select “Space” as the Delimiter, and amend the Text qualifier to “None”

Click Next.

In this step, you can change the data type of the column.  Leave them as they are and click Finish.

Split Text String by Space

To split a text string at a space or comma, we can use the FIND, LEFT, MID and RIGHT functions.

LEFT and FIND Functions

First, we can find the LastName by using the LEFT and FIND functions.

=LEFT(B3, FIND(" " , B3))

 

MID and FIND Functions

We can then use the MID and FIND Functions to find the second word in the text string.

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

 

RIGHT and FIND Functions

Finally, we can use the RIGHT, LEN and FIND Functions to find the third word in the text string.

=RIGHT(B3, LEN(B3)-FIND(" ", B3,FIND(" ",B3)+1))

Split Text String by Comma or Other Character

You can use the same formulas to find commas or any other characters in a text string and split the text string accordingly.

The formulas would be slightly different

Find the First word

=LEFT(B3,FIND(",",B3)-1)

To find the second word

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

To find the third word

=RIGHT(B3, LEN(B3)-FIND(",", B3,FIND(",",B3)+1)-1)

Split Text String with Text to Columns in Google Sheets

Google sheets also has a Text to Columns feature.

Select the text you wish to split, and then click on the Data menu > Split text to columns.

Click on Detect Automatically

Select the Space.

Your text will be split into columns.

Split Text String by Comma or Other Character

You can use the FIND, LEFT, MID and RIGHT functions in Google Sheets as you do in Excel to split the text.

The formulas to extract the data for the LastName, FirstName and Department Columns are the same ones that are used in Excel.