How to split text in google sheets

Slicing and dicing data is quite common for people working with text data in Google Sheets.

And one of the common tasks a lot of people have to do often is to split a cell in Google Sheet into separate columns (two or more than two).

A very simple example of would be when you have the first and the last name and you want to split the first and last names into separate columns.

Or when you have an address as a whole in cells and you want to separate out the individual parts such as house number, street, city, state, etc, in separate columns.

Thankfully, it quite easy to split the content of a cell in Google Sheets. And there is more than one way you can split cells and separate these into multiple columns (or rows in case your data is arranged in a row instead of a column)

In this tutorial, I will show you how you can use a simple formula and the text to columns feature to split cells in Google Sheets.

So let’s get started!

Table of Contents

Split Cells into Columns Using the SPLIT function

Google Sheets has a SPLIT function that’s well suited for… you guessed it… split the contents of the cell.

Suppose you have the dataset as shown below where you have the names and you want to split these names into first and last names.

You can easily do this using the below SPLIT formula (and copy for other cells in column B):

The above SPLIT function takes the cell reference as the first argument and the delimiter as the second argument.

In this example, since we want to split the cell content before and after the space character, I have specified a space character (in double-quotes) as the second argument.

Similarly, if you have a column that has an address (where each address element is separated by a comma), you can use the comma as the delimiter to split the address into different columns.

Note that this is an array formula. So you can not remove or edit a part of the formula result. You will have to delete the entire formula result. In case you want to edit part of the result, you can first convert the result into static value and then edit it.

One benefit of using the SPLIT formula is that it gives you a result that is dynamic. This means that if I go and change any of the names, the result would automatically update.

Also, if you add more records to the data, all you need to do is copy and extend the formula for these additional records and it will split these as well.

But in some cases, you may not want dynamic results and need to quickly split the cell content into columns. In such cases, you can rely on another useful feature in Google Sheets – Text to Columns

Using Split Text to Columns

Suppose you have the below dataset again, and you want to split the address into individual elements in separate columns.

The intent here is to split the address in each cell into separate columns so that we get the house number in one column, street in the second column, the city in the third column, and so on.

Also, note that each of these address elements is separated by a comma.

Below are the steps to split the cell into multiple columns using Split Text to Columns feature:

1. Select the cells that have the address that you want to split
2. Click the Data tab
3. Click on Split Text to Columns option
4. In the Separator dialog box, select comma as the separator

You will notice that the content of the cells has been split into columns based on the delimiter.

Also, you may get the desired result after the third step only as Google Sheets sometimes correctly guesses the delimiter and show you the split cell content right there. If that’s what you wanted, you can skip the fourth step (where you manually specify the delimiter)

You can also watch the video below to learn more about how to split cells using text to columns

In some cases, you may have a dataset, where the delimiter is not a comma, but a mix of say comma and space. In such a case, you can consider using Split Text to Columns twice, once to split the cell based on the comma and then a second time using space.

So these are two really simple and fast ways to split cells into columns in Google Sheets.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may find useful:

Written by Valentine Schelstraete

Spreadsheets have become the go-to solution for storing and handling data. And spreadsheets are not limited to numbers — they provide all the tools you need to manage text-based data as well. In this article, you’ll learn about the SPLIT function in Google Sheets. It helps you separate a text string based on a delimiter.

This means you can divide text (that’s currently in one cell) into separate columns. Imagine that you have a list of contacts in your spreadsheet. Each cell contains the contact’s first name and surname and you want to divide this into two separate columns: first name and last name. The SPLIT function is the solution.

Here’s how it works:

Syntax

=SPLIT( text, delimiter, [split_by_each], [remove_empty_text] )

• text – the text or string that you want to split.
• delimiter – a single character or a group of characters that the function should consider for splitting the text. By default, each character within the delimiter is considered individually. For instance, if the delimiter is “one”, then the text is divided around the characters “o”, “n”, and “e”. If you do not want this behavior, you can set the split_by_each to FALSE. Note that the delimiter will not be included in the output of the function.
• split_by_each – [ OPTIONAL – TRUE by default ] – if you go with the default option, then the function considers each character within the delimiter string to split the text. If you set this to FALSE, then the function considers the delimiter as a whole. You can select this parameter by writing TRUE or FALSE, or by indicating 1 or 0, respectively.
• remove_empty_text – [ OPTIONAL – TRUE by default ] – this parameter indicates whether or not the function should remove empty text fragments from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cell values are added between consecutive delimiters. You can select this parameter by writing TRUE or FALSE, or by indicating 1 or 0, respectively.

How to use the SPLIT function in Google Sheets

The syntax looks a lot more complex than it actually is.

So, to get you comfortable with the function, I’ll show you some examples. Take a look at the screenshot below.

You can see that the results that I get are the input text, broken into pieces at the points where the delimiters are in the text. For the first example, the delimiter is “e”, so the result is one cell with all the text before the e’s in “Sheetgo”, and one cell with all the text after the e’s. I achieve that here by typing =Split(A2,B2).

The same is true with the other examples shown. In each, I specify a delimiter, and the function breaks the text into chunks that come before, in between, and after the delimiters. This can be extremely convenient for separating text data such as first and last names separated by a comma, as seen in the third example above.

Tip: Leave empty columns to the right

I have entered all the functions in column D. That’s because the SPLIT function in Google Sheets spreads its output across multiple cells towards the right, as is the case with all the examples. So, it is very important that you keep the cells, where you expect the data to flow, clear of any values. Otherwise, the function returns a #REF! error.

These first few examples show the SPLIT function in its most basic form. I didn’t specify the optional parameters, so they remained at their default value. Now I’ll demonstrate what happens when you add in the optional parameters split_by_each and remove_empty_text.

SPLIT function with split_by_each parameter

The split_by_each parameter is used when the delimiter is more than one character long. As you can see in the image below, changing this parameter can have a significant impact on the output of the function.

In this example, I am splitting the word “Countries” using the delimiter “one”.

The default value of the split_by_each parameter is 1 (TRUE), which tells the function to consider each character in the delimiter. So when you use this option, the function uses the “o”, “n”, and “e” as individual delimiters. You can see the result above: the text “Countries” is split into four parts.

The next example uses a split_by_each parameter value of 0 (FALSE), which tells the function to consider the delimiter text as one single delimiter of multiple characters. So in this case, the function will only split the text based on the delimiter of the entire word “one”. Of course, “one” does not appear in “Countries”, so it does not split the text at all. This goes to show the large difference in results that occurs when you change the split_by_each parameter when using a multi-character delimiter.

SPLIT function with remove_empty_text parameter

Now I’ll show an example using the remove_empty_text parameter. This parameter is used when you have consecutive delimiters in your text.

If the parameter is TRUE (default value) it will treat consecutive delimiters as a single delimiter; if FALSE, it will treat them as individual delimiters and add blank cells to the results.

You can see the difference here. I have a text string that contains consecutive delimiters — in this case, two commas beside each other.

In the default case, where the remove_empty_text parameter is 1 (TRUE), the function treats the consecutive commas as one and outputs the text split up as expected.

In the second case, however, I’ve set the remove_empty_text parameter to 0 (FALSE). Now, the function treats each comma as a separate delimiter, so it adds a blank cell to the output to reflect the “space” (even though there is no space character) in between the two commas.

SPLIT function: Things to remember

There are a few things to keep in mind when using the SPLIT function. First, always make sure you have enough blank cells to the right since the SPLIT function can output across multiple cells.

Second, remember that the delimiter will always be removed from the text. For example, if you want to separate text strings by commas, the comma will not appear in any of the output cells.

Lastly, you can also split text by selecting Data > Split text to columns. This method is less versatile, however, so you may be better off using the SPLIT function – play around with both options to see for yourself!

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

Watch Video – How to Split Text to Columns in Google Sheets

You can use the Split Text to Columns in Google Sheets to quickly split the contents of a cell (or a range of cells).

This Article Covers:

Split Text to Columns in Google Sheets

Split Text to Columns feature comes in handy when you want quickly split the first name and the last name, or the username and domain name from email id, or the domain name from URLs.

In this tutorial, I will show you multiple examples of how to split text to columns in Google Sheets.

Example 1 – Split the Full Name into First Name and Last Name

Below is a dataset with the names of some of my favorite superheroes:

Here are steps to split the full name into the first and last name:

• Select the data that you want to split.
• Go to the Data tab.
• Click on Split Text to Columns from the drop-down.
• In the Separator dialog box that appears at the bottom right of the data, select space as the delimiter.

That’s it! It will instantly split the names into the first name and the last name.

• When you use the ‘Split Text to Column’, it overwrites the original data set. If you want to keep the original dataset intact, create a copy of the data set and use Split Text to Column on that data set.
• It would give you a static result. This means, that your data would not update in case you update the original dataset. If you want this to be dynamic, use the split function.
• Every space character is considered as a different separator. In case you have a double space between names and you use the space character as the delimiter, it will split the name into 3 columns. In such cases, remove the double space by using the TRIM function [there is a text to column functionality in Excel to consider consecutive delimiters as one. I hope that is also adopted by Google Sheets].

Example 2 – Split the Email id into Username and Domain Name

Suppose you have a dataset with emails as shown below:

Here are the steps to use Split Text to Columns to separate username and domain name:

• Select the data that you want to split.
• Go to the Data tab.
• Click on Split Text to Columns from the drop-down.
• In the Separator dialog box that appears at the bottom right of the data, select Custom.
• In the Custom field, enter @.

As soon as you enter @, Google Sheets will instantly split the text into username and domain name.

Again, remember that this will overwrite the original dataset. If you want to keep the original data set intact, create a copy, and then use the Split Text to Columns feature.

Example 3 – Get the Domain name from the URL

Suppose you have a dataset as shown below:

Note that there is a mix of URLs where some only have the root domain and some have links to a specific page/post.

Here are the steps to get the domain name from URLs using Split Text to Columns:

• Select the data that you want to split.
• Go to the Data tab.
• Click on Split Text to Columns from the drop-down.
• In the Separator dialog box that appears at the bottom right of the data, select Custom.
• In the Custom field, enter /

Note that as soon as you enter /, the URLs will spit and the domain name would be in column C.

Now if you’re wondering why column B is empty, it’s because there are two forward slashes after HTTP in the URLs. Each forward slash is treated as an individual separator.

Also, note that this technique works when you have the domain names in the same format. For example, if you have one with HTTP and one without it, then it may give you the domain names in different columns.

So this is how you can use the Text to Columns functionality in Google Sheets to quickly split the data. You can also do similar text to the column using formulas (such as RIGHT, LEFT, MID, etc.). but in most cases, I find using this a lot easier.

I hope you found this tutorial useful!

You May Also Like the Following Google Sheets Tutorials:

Watch Video – How to Separate First and Last Name using Split Text to Columns

Google Sheets makes life easy when it comes to doing some data slice and dice. It has tons of formulas and functionalities (some even better than the ones in Microsoft Excel spreadsheets).

One such area where you may need some formula intervention could be when you need to separate names into first name and last name (or email address into the username and domain name).

In this tutorial, I will show you three simple and easy ways to separate a name into First and Last name in Google Sheets.

Table of Contents

Using the SPLIT formula

Google Sheets has an inbuilt function called SPLIT that allows you to instantly split the text based on a delimiter.

In case of names, the delimiter is a space character. This means that the formula uses the space character to split the full name into first and last name (or however many parts are there to the name).

Let’s say you have a data set as shown below:

To split the full name and separate into first and last name, use the below formula in cell B2 in Google Sheets

As soon as you enter this formula and hit enter, it will automatically split the name into first and last name. It will put the first name in cell B2 and the last name in cell C2.

You can now copy and paste this formula in all the cells in column B, and it would automatically fill the column B and C with first and last name.

Instead of copying the formula manually, you can also use the fill handle and drag it for all the cells in which you want the formula.

Note that when you use this formula, Google Sheets will not allow you to delete only the last name for any name. You can choose to delete the first name in Column B or both first and last name, but not just the last name.

This is because when you enter the formula in any cell in column B, it automatically adds the one in column C (based on the formula). So the last name is a part of the formula, and since the formula in column B, you will have to delete it from there only.

One benefit of using the SPLIT function to split names (or any other text) is that it makes your results dynamic. This means that if you change any name, the results will automatically update to reflect the change.

If you only want to get a part of the text (let’s say only the first name or only the last name), use the TEXT formulas method shown later in this tutorial.

Using Split Text into Columns Feature

Another powerful feature in Google Sheets is the ‘Split Text into Columns’ feature.

Split Text into Columns allows you to specify the delimiter that you want to use to split the text (names in our example).

Google Sheets then automatically uses the specified delimiter and splits the text.

Suppose you have the names as shown below, and you want to separate the name into first name and last name.

Here are the steps to use ‘Split Text into Columns’ to separate first and last name:

1. Select the cells that contain the name that you want to split
2. Click the Data tab
3. Click on ‘Split Text into Columns’ option
4. In the Separator box that appears, select Space as the delimiter

The above steps would instantly split the full name into first and last name (in different columns).

Below is a video where I cover how to use Text to Column to split the first and last names in Google Sheets.

Using Text Function

While SPLIT function is great, it gives you the entire split of the text.

For example, if say you only want the first name or only the last name, then you can’t do it directly with the SPLIT function. You can still use it by converting the formula to values and then deleting the cells you don’t want, but that just makes it less efficient.

In such cases, you can use the wonderful TEXT functions that Google Sheet has.

These Text functions allow you to extract only that part of the text that you want.

Let me show you how Text function works with examples.

Suppose you have the data set as shown below:

You can use the below formula to extract only the first name from the full name:

The above formula uses the FIND function to get the position of the space character in the name. For example, in the name ‘Jesusa Owenby’, the space character is in the seventh position.

Now we use this position number to extract all the characters which are to the left of it, using the LEFT function.

Similarly, if you only want the last name, you can use the following formula:

The above formula uses the same concept, with a slight twist. Since we need to get the last name, we need to find the number of characters to get after the space character.

So, I have first used the FIND function to get the position of the space character and then used the LEN function to find how many characters are there in the name. A simple subtraction of FIND formula result from the LEN formula result gives me the number of characters in the last name.

Then I simply use the RIGHT function to get all the characters from the right (after the space character).

The TEXT functions allow you a lot of flexibility and you can handle a lot of scenarios with these type for formulas. For example, if you have a mix of names with first/last name and ones with a middle name as well, you can still use a variation of these Text formulas to get the first and the last name.

You may also like the following Google Sheets tutorials:

• Excel Tips
• Excel Functions
• Excel Formulas
• Word Tips
• Outlook Tips

How to split cell contents into columns or rows based on newline in Google sheet?

If you have a list of text strings which are separated with a carriage return, and now, you want to split the cell contents into columns or rows by the newline in Google sheet. How could achieve it as quickly as possible?

To split the cell contents into columns or rows by carriage return, please apply the following formulas:

Split cell contents into columns based on carriage return:

1. Enter this formula: =split(A1,char(10)&”,”) into a blank cell where you want to output the result, and then press Enter key, the text strings have been split into multiple columns as following screenshot shown:

2. Then drag the fill handle down to the cells which you want to apply the formula, and all cell contents have been separated into multiple columns as you need, see screenshot:

Split cell contents into rows based on carriage return:

Please apply this formula: =transpose(split(join(char(10),A1:A4),char(10))) into a blank cell to output the result, and then press Enter key, all the cell contents have been split into multiple rows as following screenshot shown:

In Microsoft Excel, the utility Split Cells of Kutools for Excel can help you to split cell contents into columns or rows based on space, comma, semicolon, newline and so on.

After installing Kutools for Excel, please do as follows:

1. Select the cells that you want to split by newline, and then click Kutools > Merge & Split > Split Cells, see screenshot:

2. In the Split Cells dialog box, select Split to Rows or Split to Columns as you need in the Type section, and then select New line from the Split by section, see screenshot:

3. Then click Ok button, a prompt box is popped out to remind you to select a cell to put the split result, see screenshot:

4. Click OK, and you will get the following result if you choose Split to columns option in the dialog box.

If selecting Split to rows option, the cell contents will be split into multiple rows as below screenshot shown:

A lot of times, you may want to split the text strings into two or more columns in Google Sheets.

For example, if you have names, you may want to split the first name and the second name.

Similarly, if you have an address, you may want to split the address elements (such as house numbers, street, city, state).

Split Text in Google Sheets

In this tutorial, you’ll learn how to split text in Google Sheets:

• Using the SPLIT function.
• Using the Text to Column feature.

Split Text in Google Sheets Using the SPLIT Function

Here I have a list of some of my favorite fictional characters.

To separate the first name and last name, use the following formula in cell B1:

This would instantly split the name and give the first name in cell B1 and last name in cell C1. Now copy paste it for all the cells or drag it down to apply the formula.

Here is how it works:

SPLIT function takes two arguments:

• The first argument is the text which you want to split.
• The second argument is the delimiter that is used to separate the text strings.

In the above example, the delimiter is a space character.

NOTE: The benefit of using this method is that it is dynamic. If you change the name in any cell, the result would dynamically update.

Also, you won’t be able to delete a part of the result. For example, you can not delete only the last name.

Split Text in Google Sheets Using Text to Column Feature

Another quick way to achieve the same result is using the Text to Column feature in Google Sheets.

Again let’s take the same example of fictional characters.

Here are the steps to split the first name and the last name:

• Select all the cells in which you want to split the text.
• Go to Data –> Split text to columns.
• In the Separator drop-down. select the delimiter (which is space in this example).

This would instantly split the names into first name and last name.

NOTE: This technique alters the original data set. If you want to keep the original dataset intact, make a copy. Also, the results are not dynamic, which means that if you make changes, you’ll have to repeat this process again.

You May Also Like the Following Google Sheets Tutorials:

Below are the steps on how to change the alignment of text within a cell in the most popular spreadsheet programs. View the steps for the program you are using by clicking one of the links below.

• Align text in Microsoft Excel.
• How to align text in Google Sheets.
• Aligning text in OpenOffice Calc.

Align text in Microsoft Excel

You can change the horizontal alignment of text in a cell using options on the Microsoft Excel Ribbon.

1. Select the cell or which you want to change the alignment.
2. In the Ribbon, on the Home tab, select the type of alignment you want to use, as shown in the picture above.

For example, if you wanted to center the text in a cell, click the center icon.

To change the alignment of text in a cell using a keyboard shortcut, follow the steps below.

1. Select the cell you want to change.
2. Press Alt + H .
3. Press A.
4. Press L for left align, C for center, or R for right align.

Adjusting the vertical alignment

Change the vertical alignment of a cell’s text by following the same steps above, but select Top Align, Middle Align, or Bottom Align.

You can also use the Orientation button to make the text vertical, at an angle, or any orientation you desire.

How to align text in Google Sheets

In Google Sheets, to change the horizontal alignment of text in a cell, select the cell and click the Horizontal Align button on the toolbar (shown above). Once done, you’ll have the option to select Left, Center, and Right alignment.

Press one of the shortcut keys to adjust the alignment of any selected cell. For left alignment, highlight the text and press Ctrl + Shift + L . For center alignment, highlight the text and press Ctrl + Shift + E . For right alignment, highlight the text and press Ctrl + Shift + R .

Adjusting the vertical alignment

To change the vertical alignment of text in a cell, click the Vertical Align icon in the toolbar (as shown above). Once selected, choose Top, Middle or Bottom, depending on how you want to align the text.

You can also use the Text Rotation option (shown above) to make the text vertical, a tilt, or at any angle you desire.

Aligning text in OpenOffice Calc

Change the horizontal alignment of cell text in OpenOffice Writer by highlighting the cell and clicking the left, center, or right align icons in the top toolbar. These icons look similar to those shown in the above example picture of Microsoft Excel.

Adjusting the vertical alignment

OpenOffice calc does not show a vertical align option in the top toolbar. However, vertical alignment options are shown in the Properties window appearing to the right of the spreadsheet when a cell is selected. From this window, you can select Align Top, Align Center Vertically, and Align Bottom.

April 30th, 2019

Written by Jane Hames

Sometimes you might find that the content of text cells in a spreadsheet isn’t laid out the way you’d like it; for example a column may contain whole names where you’d prefer forename and surname to be in separate columns, or it contains full addresses where you’d want the first line, city, county etc to each be in its own column.

The “Split text into columns” feature in Google Sheets, makes it quick and easy to split the text across multiple columns. All you need is a marker to identify where the data should be split. This could be a comma, a space or any other character.

For example I have this data, where the town and county are combined in one location cell.

I will want to analyse my customers by county, so in preparation for my analysis I am going to split the town and county in to separate columns.

Step 1:

Check that you have enough blank columns next to the data you want to split. If you already have data in the adjacent columns, that will be replaced by the split data. In my example I need a blank column for the county to go into, as shown in green here:

Step 2:

Select the data that needs to be split. As you can see, I have selected the data in column B here:

Step 3:

From the Data menu, select Split text into columns.

Step 4:

Sheets will try to detect where you want the split to occur. In my example, I have a comma between each town and county, so this is where it splits.

Step 5:

If you want the split to occur in a different position or if Sheets cannot detect automatically, click on “detect automatically”. Select the separator that suits your data or click on Custom and type the character you require as your separator.

And that’s it! In just a few seconds you have your data organised how you want it.

Sometimes, your data comes ith several bits of information in one column. Like a column with US states in the format US-TX . Or a column with companies and the product they sell: Datawrapper (Software) .

Buy say you want contry ( US ) separate from state ( TX ), e.g. to create a Datawrapper choropleth map. Good thing there are easy ways to separate data points into two or more columns.

I’ll show two ways to create several new columns out of one column. To do so, we’ll use Google Sheets – but this should work with LibreOffice Calc, Excel or any other spreadsheet software.

The first method is the formula =SPLIT() :

Split columns with SPLIT()

1. Create at least two columns next to the column with the data you want to split. You can do so, click on the header ( A , B , C , etc.). Then click the little triangle and select “Insert 1 right”. Repeat to create a second free column.
2. In the first free column, write =SPLIT(B1,”-“) , with B1 being the cell you want to split and – the character you want the cell to split on. (If you see the error #REF! in your cell, you’ll need to create more columns.)
3. To apply the changes to the cells below, drag down the blue square in the bottom right of the selected cell(s). Double-click on the blue square to fill all remaining cells.

Extract content from columns with LEFT()

Sometimes you don’t have clear separator characters, but just want to extract the first or last characters of a cell. To do so, use the formulas =LEFT(B1,2) , =RIGHT(B1,8) and =MID(B1,2,4) :

1. Insert a new column. (Or two. Or three! As many as you need.)
2. In the new column(s), write

=LEFT(B1,2) to extract the first 2 characters of the cell B1.

=RIGHT(B1,8) to extract the last 8 characters of the cell B1.

=MID(B1,4,2) to extract the 2 characters following the 4th character in B1.

Pro tips

Pro tip 1: You can combine formulas to extract characters at all sorts of crazy positions. For example, the formula =LEN() gives back the number of characters in a cell. So =LEFT(A1,LEN(A1)-2) extracts the entire text in a cell except the last two characters. To separate the cell Datawrapper (Software) into the two cells Datawrapper and Software , you could use the formula =SPLIT(LEFT(A5,LEN(A5)-1),”(” . This formula first removes the last bracket and then splits the remaining cell content on ( .

Pro tip 2: Now that you learned to separate text, you can also bring it together again. To combine the column US from your cell A1 and TX from B1 with a hyphen, use ampersands and write =A1&”-“&B1 .

Pro tip 3: You can also extract content with LEFT() , RIGHT() and MID() not just from text cells, but also from number and date cells. If you want to apply formulas like LEFT() to your dates, it helps to transform them to the text format first. To do so, use the formula =TEXT(A1, “MM/DD/YYYY”) . Instead of MM/DD/YYYY , you can use any combination of these date codes and / , – , a space, etc. For example, =TEXT(A1, “dd-mmm-yyyy”) will transform the date format 1st of November 2019 to a text cell with the content 01-Nov-2019 .