Learn how to split text into separate cells or combine text into a cell using the split and concatenate functions in 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).
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).
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).
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):
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.
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:
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.
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).
- 5 important tasks for G Suite administrators (TechRepublic)
- 9 awesome G Suite features that its designers wish more people knew about (ZDNet)
- Zoom vs. Google Hangouts: Video chat apps for working and keeping in touch compared (CNET)
- Microsoft Office vs Google Docs Suite vs LibreOffice (Download.com)
- How-To Tips: More easy-to-follow tutorials (TechRepublic on Flipboard)