How To Split Cells In A Spreadsheet

Have you ever had a spreadsheet where you'd really like to split a cell that contains multiple data points (last name, first name) into individual cells with only one data point each? I know that I used to use this a lot for taking my class roster and separating the last and first names. Today I'm going to show you how to use the Text to Columns feature, in both Google Sheets, and Microsoft Excel to do just that.

Google Sheets:

As you might expect, with Google Sheets generally being a simplified version of Excel, the process of splitting text to columns is fairly simple.

1. Right click on the column to the right of the column that contains the text you'd like to split.
2. Choose the option Insert 1 left; you're going to need to do this for each additional word. Since I will end up with two separate words in this example, I only need to do this step once as I only need one extra cell.
3. Highlight the cells that you want to split.
4. Click the Data heading from the toolbar.
5. Click Split text to columns. This will take the first name and place it into the second column that you just added.

You'll notice that, at the end of the GIF, underneath the first name column a drop down menu appears with the word Comma selected, this is how you choose where to make the split. In this case, the last name and first name are separated by a comma; some other options to split include semicolon, period, and space. 

Microsoft Excel:

Since Microsoft is a much more robust program than Google Sheets, there are a few more steps that allow you to do more with your text to columns. I'm going to focus on just the basics in this post though. The beginning steps are the similar up until you click on the Data tab:

1. Right click on the column to the right of the column that contains the text you'd like to split.
2. Choose the option Insert; you're going to need to do this for each additional word in the cell. Since I will end up with two separate words in this example, I only need to do this step once as I only need one extra cell.
3. Highlight the cells that you want to split.
4. Click the Data heading from the toolbar.
5. Click the Text to Columns option, this is where things start to get a little more complex.
6. Unlike Sheets, Excel makes you choose what you want to use to separate your text. 
1. In this instance, I chose Delimited since the last name and first name were separated by a column. You would also use Delimited if the words were separated by a space, semicolon, tab, etc.
2. If I had wanted to split a string of text at the same place in each cell, I could have chosen Fixed width and simply chosen where I wanted the break to happen.
7. I changed the check box from semicolon to comma and then clicked Next. I really could have clicked Finish at this point if I wanted to as I didn't make any changes on the next screen.