3 easy ways to split cells in Google Sheets to multiple columns

This guide provides several methods for splitting text within Google Sheets cells into multiple columns. We'll explore the built-in tool, a powerful add-on, and the SPLIT function.

If your cells contain multiple words, splitting them into separate columns simplifies data filtering and sorting. Let's examine the options:

1. Google Sheets' Built-in "Split text to columns" Tool: This tool is straightforward for simple tasks, but has limitations.

   1. Select the cells needing splitting.
   2. Navigate to Data > Split text to columns.
   3. A panel appears (see below) offering common delimiters (comma, semicolon, period, space) or a custom option, or automatic detection.
   4. Choose your delimiter (e.g., space). The text will be split into adjacent columns.

   Limitations: This tool overwrites existing data in adjacent columns, requiring pre-emptive empty column insertion. It also only handles one delimiter at a time. For instance, splitting "Chocolate, Extra Dark" requires two steps: first by comma, then by space.

2. Ablebits Power Tools "Split Text" Add-on: This add-on offers a superior solution, handling multiple delimiters simultaneously and providing more control.

   This method allows splitting by various separators: spaces, line breaks, commas, semicolons, custom symbols, text strings ("and," "or," etc.), and even capital letters. Key advantages include handling consecutive delimiters and the ability to choose whether to overwrite the source column. It even allows splitting into rows.

   1. Select the cells.
   2. Configure delimiters.
   3. Adjust settings (two checkboxes).
   4. Click "Split."

3. The SPLIT Function: This function divides text based on a delimiter. Its syntax is SPLIT(text, delimiter, [split_by_each], [remove_empty_text]).

   • text: The text to split (cell reference or string).
   • delimiter: The separator character.
   • split_by_each (optional): TRUE (default) splits at every delimiter; FALSE splits only at the first.
   • remove_empty_text (optional): TRUE (default) removes empty cells created by consecutive delimiters; FALSE keeps them.

   Example: =SPLIT(C2," ") splits the contents of cell C2 by spaces. Using ARRAYFORMULA allows splitting an entire column at once: =ArrayFormula(SPLIT(C2:C69," ")).

4. Additional Ablebits Power Tools Features: Beyond character-based splitting, Ablebits offers:

   • Split by Position: Useful when delimiters are absent or you need to extract specific character lengths.
   • Separate First and Last Names: Intelligently handles various name formats, including middle names and titles.
   • Split Date & Time: Separates date and time components from combined cells.

Ablebits Power Tools is a highly recommended add-on for versatile text splitting in Google Sheets. Consider installing it from the Google Marketplace for enhanced functionality.

The above is the detailed content of 3 easy ways to split cells in Google Sheets to multiple columns. For more information, please follow other related articles on the PHP Chinese website!