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

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

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

split text space comma Main Function

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.

Select Data

In the Ribbon, select Data>Text to Columns.

Data TexttoColumn

Keep the option “Delimited” selected and click Next.

Delimitted

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

Step 01

Click Next.

Step Last

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

Text To Column Result

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.

Exact Name Final

LEFT and FIND Functions

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

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

Find First Word

 

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

Find Second Word

 

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

Find Third Word

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.

Google 01

Click on Detect Automatically

Google 02

Select the Space.

Google 03

Your text will be split into columns.

Google 04

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.

Google 05

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List