Split column into multiple columns Google Sheets
Draft
To do:
- Add alt text to images
Task: Copy/paste text with space delimited columns into Google Sheets.
Problem: Unless steps taken, Sheets will merge all columns into a single column.
Example text to copy/paste
Spp1 Spp2 Distance
Alligator Chicken 0.3300730
Alligator Human 0.5120823
Alligator Mouse 0.4554709
Alligator Xenopus 0.6868813
Chicken Human 0.4373133
Chicken Mouse 0.3807019
Chicken Xenopus 0.6121123
Human Mouse 0.1515146
Human Xenopus 0.6708030
Mouse Xenopus 0.6141916
Example, merged columns in Google Sheets (Fig 1).
Figure 1. Screenshot from Google Sheets, three columns of text merged into single column.
Tips to copy/paste into Google Sheets
To paste text into separate columns in Google Sheets, copy the source text into your computer’s clipboard, then paste the text into the spreadsheet. Before submitting, highlight the cells/column containing the text. Next, go to “Data”, then “Split text to columns”, and choose the delimiter (like comma, space, etc.) to separate the data. If copying text from the clipboard you should see a little clipboard icon popup at lower right of the highlighted text. You can access the menu to manage paste into columns from there.
However, if multiple spaces separate the columns, or if there are variable numbers of spaces, then this option results in a patchwork of empty cells and too many columns. The rest of this page provides a simple solution with use of the trim() function first, followed by use of the Text to columns… menu option.
The following instructions assumes that your copy/paste results look like Figure 1.
1. First step, remove extra spaces with the trim() function (Fig 2).
Figure 2. Screenshot of Google Sheets with trim function entered into cell B1 focused on text in cell A1.
3. Autofill or drag to complete. Your spreadsheet should look line Fig 3.
Figure 3. Screenshot of Google Sheets with trim function entered into all cell of the B column.
4. Select the B column, then from the menu, select Data > Split text to columns. The option is towards the bottom of the popup menu. Once selected, a small options box is visible (Fig 4).
Figure 4. Screenshot Google Sheets, separator options visible.
5. Click on the Separator box and select Space, in our example.
6. Confirm the text is split into the columns correctly, then click elsewhere in the document to close the action.
Other spreadsheet apps
Microsoft Excel, LibreOffice Calc, etc each handle copy/paste of text in different ways, but trim() and Text to Columns options are shared by these apps