How to split or combine text cells with Google Sheets

Learn how to split text into separate cells or combine text into a cell using the split and concatenate functions in Google Sheets.

How to split or combine text cells with Google Sheets

Many people use Google Sheets to take text from a single cell and split the contents into different cells in different columns, or accomplish the reverse action, where you take text from separate cells and combine it into one. For example, you might split cells to separate names (e.g., first name, last name) or combine contents to prepare text to export for use in a Google Doc (e.g., to combine a description with detailed text).

But too often, people split or combine text manually. Select a segment, copy it, then paste it into another cell. Or, meticulously r-e-t-y-p-e t-e-x-t, c-h-a-r-a-c-t-e-r b-y c-h-a-r-a-c-t-e-r for each cell you want to split or combine. Both of these manual methods t-a-k-e t-i-m-e.

SEE: Top cloud providers in 2020: AWS, Microsoft Azure, and Google Cloud, hybrid, SaaS players (TechRepublic Premium)

Google Sheets on the web offers a menu option that lets you split text quickly. However, in the mobile apps, to combine cell contents or to split text, you'll need to enter a function (Figure A). 

Figure A

Screenshot of Google Sheet with Split and Concatenate functions displayed: =SPLIT(A3," ") and =CONCATENATE(A9," ",B9," ",C9) with sample content text of names

Functions let you split or combine text in Google Sheets. Use =SPLIT to separate text from one cell into separate items in separate cells. Use =CONCATENATE to append text from cells into a single cell.

Before you begin, open the Sheet with the text content you want to split or combine, then follow the steps below.

How to split text into separate cells

The Split Text function in Google Sheets takes text contents of a cell, then splits it into segments and places those segments into separate columns to the right of the original column. You may specify the character you want to use to segment sections of text or let the system auto-select separators. For example, often, you'll demark text segments with a space (e.g., " "), but other default separator options include a comma, semicolon, or period.

In a desktop-class class web browser, you may access Split text as a menu option.

1. Select the cell or cell range you want to split.

2. Choose Data | Split Text To Columns (Figure B).

Figure B

Screenshot with Data menu displayed, and cursor on Split text to columns option.

In Google Sheets on the web, select cells, then choose Data | Split Text To Columns.

3. To specify the character used to delineate segments, select the drop-down menu, then either choose from the separator list or select Custom to enter an alternative separator.

4. Google Sheets splits your text as specified. 

Review the contents of the text in the separated cells. In some cases, you may want to make minor adjustments. For example, if you use a space as a separator, then "Dr. Susan Smith," "Susan Smith, PhD," and "Sam Smith III," would split text into three cells. If most of your other source cells contain only "FirstName LastName," you may need to manually modify the results.

In Google Sheets on mobile devices (Android and iOS), enter the SPLIT function into a cell to separate string segments (Figure C). 

Figure C

iOS screenshot that shows "FirstName LastName" in cell A2, and formula =SPLIT(A2," ") in cell B2, resulting in FirstName in cell B2, and LastName in cell C2.

In a cell next to the content you wish to split, enter the =SPLIT function and specify a cell and delimiter. The system will then fill in cells with the split text segments.

Enter this formula to the right of the source cell you wish to split. For example, if A2 in your sheet is the cell you want to split where separated by a space, you would enter (in cell B2): 

=SPLIT(A2," ")

Put the separator you wish to use within quotes in the function (e.g., "-"","".", or ";", etc.). Copy and paste the function as needed throughout your spreadsheet.

How to combine text into a cell

To combine text from two or more cells into a single cell, use the CONCATENATE function, which you may enter in Google Sheets on the web (Figure D) as well as the mobile apps. This lets you specify source cells and also add text and or separators before, between, or after the cells that you choose to combine.

Figure D

Google Sheets screenshot on the we that shows, for example, three pieces of content, Dr. (column A), Susan (column B), Smith (column C) concatenated into an entry in column D: "Dr. Susan Smith".

Enter the =CONCATENATE function to append contents from two or more cells into a single cell. In this screenshot, content from columns A, B, and C combine into an entry in column D.

For example, I used the concatenate function in an application feature comparison spreadsheet. One column contained feature names, while another column contained application details. I combined these and added a colon separator, then copied that text into a Google Doc, with the following function:

=CONCATENATE(A3,": ",B3)

This takes the contents of A3, adds a colon and space, followed by the contents of cell B3. The combined contents are placed in the cell where I entered the function (i.e., cell C3). Specify cells by entering the cell references, separated by commas and surround any spaces and/or separators you wish to add with quotes.

Your experience?

Have you manually split or combined cell contents with Google Sheets? Or have Split Text, =SPLIT, or =CONCATENATE helped you quickly separate or combine text cell contents? Let me know how these features have helped you manage data in Google Sheets either in the comments below or on Twitter (@awolber). 

Also see

Twe sets of three Sheet cells: (top) title Split, with left cell containing "Google Sheets", middle cell "Google", right cell "Sheets" (shows text split); (bottom) title Concatenate, left cell "Google", middle cell "Sheets", right cell "Google Sheets" (shows text combined)

Illustration: Andy Wolber / TechRepublic