How To Split Cells In Google Sheets?

Are you having trouble splitting cells in Google Sheets? You're not alone. Google's online spreadsheet application is a great tool, but splitting cells can be tricky. I've built my fair share of spreadsheets and tested a lot of methods of splitting cells in Google Sheets. This has led me to creating my own list of go-to split cell methods.

Have you encountered a data entry task wherein you need to sort out the content into another set of columns and divide them by categories accordingly?

For example:

A full name” that needs to be separated into first, middle, and last names in separate columns

• An “address” divided into street number, city location, state location, and country.
• A “birthdate” being split into the month, day, and year.

Those are some of the data entries you will commonly encounter wherein splitting them by manually typing the entry is tiresome. Fortunately, Google Sheets have 2 methods you can use to efficiently separate the data entries into separate columns.

How to Split Cells?

1).  Using the “SPLIT Function”

1. Let us look at the example below. To separate the two-word contents from cells “A2” to “A6” (for this example, a full name) into one-word contents on the 2 columns “B” (first name) and “C” (last name) respectively, we will be using the “SPLIT” function. Note that another term for the separator is the delimiter.
   
2. Type the formula =SPLIT(A2,” ”) in the function bar for B2. This means that the content in A2 (full name), where it is separated into two words by the space between, is being copied and split into 2 columns. The first word, whatever it is, will be copied in the B2 cell and the second word will be copied in the C2 cell automatically.
   
3. If in the full name column, the first name and last name are separated by a comma instead of the space, then the formula to be used will be =SPLIT(A2,”,”). If the separator or delimiter is a letter “x” instead of space or a comma, the formula will now be like this: =SPLIT(A2,”x”).
4. You can repeat the process respectively by dragging the B2 cell on its borderline dot towards the end row. The opposite process of the SPLIT function is the “CONCATENATE” function.

2).  Using the “Split Text to Columns”

1. In another example below, we will be separating the content (the address) wherein the words are separated by a comma and space. Instead of typing a function, we will use the “Split Text to Columns” button.
   
2. Highlight the cells you need to separate the contents.
3. Select the “Data” tab from the menu bar, and when the drop-down menu will appear, choose “Split text to columns”.
4. A dialog box will appear asking what will be its basis for separation. Since in the original content, comma and space were used and you need to pick only one in the dialog box, you just choose “comma” because of its prominence compared to space.
    
5. As you click the “comma”, the original content (the address) is being replaced with the first word and the succeeding columns are the next words that follow after every comma.
6. If the separator or delimiter used is not a comma, then on the previous step, you will have to choose the appropriate separator or delimiter on the dialog box. The only limitation is if the original content uses different characters as separators.
   

Summary: Split cells in Google Sheets

• “Split Text to Columns”
• In the menu at the top, click Data Split Text to Columns. The Convert Text to Columns Wizard opens.
• Follow the instructions in the wizard to separate your data according to your own needs, and then click Finish.
• “Flash Fill”
• In cell B2, type the text you want in the left half of a split cell, and press Enter.
• Flash Fill will detect your pattern and fill in any other cells that need it.

Conclusion

Separating cells using the aforementioned methods works best if you have multiple entries to sort out. There can be some limitations, but for common use, the methods are already enough.