How Do I Split Columns in a Google Docs Spreadsheet?

Save

The SPLIT command lets you split cells or entire columns of cells in a Google Docs spreadsheet around occurrences of a particular character, a character from a set or a string of characters.

Splitting Around a Character

The SPLIT formula function allows you to split text in a cell every time a particular letter, number, punctuation mark or other character appears. The character you're using to split the text is called the delimiter.

For instance, if you split a cell with the text "Adam&Eve" on the "&" character, you would get two cells: one with the text "Adam" and one with the text "Eve".

Step 1: Add Columns to Contain the Split Text

(Image: Image Courtesy of Google)

When you split a cell or column of cells, the split text is inserted into the cells directly to the right of the split cells.

Make room for it by adding additional columns to the spreadsheet. Place the cursor into the column containing the cells to be split, click the Insert menu and then Column Right. Do this twice to insert two columns for the two parts of the text being split.

Step 2: Add Additional Columns For Additional Parts

If the delimiter occurs more than once, add an additional column for each additional occurrence of the delimiter to make room for all the new cells.

For instance, if you're splitting the text "One+Two+Three" around the plus sign character, you'll generate three additional cells after the split: one with the text "One", one with the text "Two" and one with the text "Three", so you'll need to add three columns.

Step 3: Start the "Split" Operation

Once you've added the right number of columns, click on the cell immediately to the right of the topmost cell to be split and start the SPLIT formula by typing =SPLIT(. Don't press the Enter key yet.

Step 4: Select the First Cell to Split

(Image: Image Courtesy of Google)

Click the topmost cell being split in order to select it. Its coordinate (e.g., A2) will appear in the SPLIT formula.

Step 5: Finish Typing the Formula

(Image: Image courtesy of Google)

With the cell selected, finish the SPLIT formula by typing a comma, i.e. "," (without the quotes) followed by the delimiter to use to split the text in quotation marks, followed by a parenthesis to end the function. Then, press Enter to split the cell.

For instance, to split the text in cell A2 around the character &, type "&"), so the entire function reads: SPLIT(A2,"&"). Press Enter to split the cells, with the portion after the delimiter moving into the adjacent cell.

Step 6: Make Sure the Newly Split Cell is Selected

(Image: Image courtesy of Google)

Click on the cell in which you were just typing, so that a square box appears in the cell's corner.

Step 7: Drag the Split Formula Down to Split the Column

(Image: Image courtesy of Google)

Click the square box in that cell's bottom right corner, and then drag this box down to the last row of the spreadsheet. Release the mouse and the entire column splits.

Splitting Around Any Character From a Set

(Image: Image courtesy of Google)

Sometimes, you may need to split text based on any occurrence of several possible delimiter characters.

For example, you might have a column in which some pairs of items are joined by ampersands and some are joined by plus signs, e.g., Adam&Eve and George+Martha.

In that case, use the SPLIT formula, but instead of typing the single delimiter within quotation marks, type each delimiter you want to use.

For instance, to split a column cell around any occurrence of a plus sign or ampersand, use the formula =SPLIT(A2,"+&"). Enter the formula in cell B2, and then drag this formula down the column.

Splitting Around a String of Characters

Sometimes you may want to split text around a multi-character string, rather than around a single character or around any single character in a set.

For example, you may have a column in which pairs of items are separated by the string " and ", e.g., "Adam and Eve" and "George and Martha".

By default, the SPLIT function treats a multi-character delimiter as a set of possible split characters, but you can pass FALSE as a third argument to the function to tell it to treat the entire string as one delimiter.

(Image: Image courtesy of Google)

For instance, to split cell A2 on the string " and ", use the formula =SPLIT(A2, " and ", FALSE).

You can also drag these types of formulas down the column to split additional cells.

Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!