When entering text data in Google Sheets, a single text string or sentence may become lengthy and would overflow into the next cell. A text split is exactly what you need.
Also, you may be entering personal data of other individuals like names, addresses, dates of birth etcetera, and you would want to split the texts for more conciseness and readability.
In this guide, we’ll learn how to split texts into cells in Google Sheets with different methods.
Split Text to Columns: A Keyboard Shortcut
A keyboard shortcut can also be used to split texts into separate columns. This is the first and quickest way to do this.
The keyboard shortcut used is ALT key + a, e.
This shortcut won’t work until you enable compatible keyboard shortcuts in Google Sheets.
You need to follow these steps to enable this setting.
1. Go to the toolbar and click on Help.
2. From the list provided, select Keyboard Shortcuts.
3. The Keyboard Shortcuts tab is opened. At the bottom left of the tab, turn on the ‘Enable Compatible Spreadsheet Shortcuts’ toggle.
4. Once you’ve enabled this feature, press the keyboard shortcut ALT key, then hold the ‘a and e’ keys.
How To Split Cells in Google Sheets by Using the SPLIT Function.
As the name suggests, the SPLIT function is specifically used to divide the text string into separate columns in your worksheet.
In the sample worksheet below, I have some sentences in column A, so I want to split these texts into separate columns in the worksheet.
The SPLIT function takes the syntax below.
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Where:
- Text – this is the text to divide.
- Delimiter – The character or characters to use to split ‘text’.
- Split_by_each [optional] – Whether or not to divide ‘text’ around each character contained in ‘delimiter’.
- Remove_empty_text [optional] – Whether or not to remove empty text messages from the split results.
Since we want to split the text in column A, the formula used is =SPLIT(A2,” “).
1. Select an empty cell where the returned value is displayed. In this case, I selected cell B2.
2. On your keyboard, press the F2 key to edit the cell and enter the formula into cell B2.
3. Hit the Return key. The text in cell A2 is split into cells in columns.
4. Drag down the fill icon cube to copy the formula and paste the returned data into their respective cells.
Here, the SPLIT function uses cell A2 as its reference and the delimiter is a space which is placed in quotation marks.
The delimiter can be any character or it could be a punctuation mark. So if you have a list of items you want to split and each item is separated by a comma, the delimiter in the formula would be replaced with a comma.
Once the formula is entered, the SPLIT function takes the delimiter as an interval to separate each item and place them in different columns.
The SPLIT function is volatile, which means, it adjusts and updates any change or removal made to the main dataset. The returned values cannot be modified.
How Do I Split a Cell into Two in Google Sheets?
When you split a cell into two, this means that you split the cell’s content into columns not literally dividing a cell into two. This is done by using the SPLIT function.
To use the function, you would follow the steps below.
The formula used is =SPLIT(A2,” “).
1. Place the cell in edit mode by double-clicking. Then, enter the formula into cell B2.
2. Hit the Enter key. The text in cell A2 is split into cells into other columns.
3. Drag down the fill icon cube to copy the formula and paste the returned data into their respective cells.
How do I Split a Cell Vertically in Google Sheets?
This is done with the use of a combination formula. The TRANSPOSE function is combined with the SPLIT function to vertically divide a cell’s contents.
For instance, we have some names in cell A2 and I want to split the names vertically on my worksheet.
The formula used is =TRANSPOSE(SPLIT(A2,” “)).
1 Input the above formula into an empty cell. In this case, I selected cell B2.
2. Place the cell in edit mode by double-clicking and entering the formula into the cell.
3. Press the Enter key on your keyboard and the name in cell A2 is divided vertically into three rows.
Final Thoughts
The SPLIT function can be very useful when it comes to sorting and editing data. Splitting text in cells would be very helpful to users who often work with word data.
Hope you enjoyed this guide and learned a couple of new facts about the SPLIT function.
