How to Split Text to Columns in Google Sheets – Amigo | Blog

How to Split Text to Columns in Google Sheets – Amigo | Blog
Reading Time: 4 minutes

Split text to columns

Objective

Learn how to split text to columns in Google Sheets and its variations.

Background

Sometimes cells contain text in the form of full name, email address or decimal numbers. You can separate these cells on various separators such as “ “, “@”, “.” or “/” by splitting the text in Google Sheets. This article covers some of the most commonly used separators and how you can split text to columns in Google Sheets.

Example 1: Splitting Full Name into First Name and Last Name

  • Let’s say you have a column where the full names look something like this:
Column of full names
Figure 1: Column of full names
  • To separate the full name into First Name and Last Name, follow these steps:
    • Select the cells.
    • Navigate to the Data button from the toolbar.
    • Click on Split text to columns.

Open Split text to columns
Figure 2: Open Split text to columns

  • Select space delimiter in the Separator dialogue box.

Select Space delimiter
Figure 3: Select Space delimiter

  • The names are separated into first and last names.

Names split into First and Last names
Figure 4: Example of how to split text to columns in Google Sheets

Example 2: Split the Email id into Username and Domain name

  • Let’s say you have a column of email addresses like this:
Column of email ids
Figure 5: Column of email ids
  • To separate the email address into Username and Domain name, follow these steps:
    • Select the cells you.
    • Navigate to the Data button from the toolbar.
    • Click on Split text to columns.

Select Split text to columns
Figure 6: Select Split text to columns

  • Select Custom separator.

Select custom delimiter
Figure 7: Select custom delimiter

  • Enter “@” as the delimiter in the Separator dialogue box.
  • The email address is separated into username and domain name.

Email ids separated by '@'.
Figure 8: Example of how to split text to columns in Google Sheets

Example 3: Split Decimal numbers into the Whole number and Fractional number

  • Let’s say you have a column of numbers like this:
Column of decimal numbers
Figure 9: Column of decimal numbers
  • To separate the numbers into Whole number and fractional parts, follow these steps:
    • Select the cells.
    • Navigate to the Data button from the toolbar.
    • Click on Split text to columns.

Select split text to columns
Figure 10: Select split text to columns

  • Select Full stop.
  • The numbers are separated into whole numbers and fractional parts.

Decimal separated into whole numbers and fractional parts.
Figure 11: Decimal separated into whole numbers and fractional parts.

Example 4: Get the Domain name from the URL

  • Let’s say you have a column of links like this:
List of URLs
Figure 12: Example of how to split text to columns in Google Sheets
  • To separate the links into internet protocol and domain name, follow these steps:
    • Select the cells.
    • Navigate to the Data button from the toolbar.
    • Click on Split text to columns.

Select split text to columns
Figure 13: Select split text to columns

  • Select Custom separator.

Select custom delimiters
Figure 14: Select custom delimiters

  • Enter “//” as the delimiter in the Separator dialogue box.
  • The links are separated into internet protocol and domain names.

Domain names separated
Figure 15: Example of how to split text to columns in Google Sheets

See Also

Google Sheets: How to Use Pivot Tables : Learning how to create and use Pivot Tables for analysing data in Google Sheets.

How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.