If a cell contains text separated by a comma or any other mark and you want to divide them into several columns, you must follow this guide. This article will help you split comma separated text in Excel and Google Sheets without using third-party software.
Suppose you have a spreadsheet containing the full names of some people and want to divide the first and last names into two columns. Instead of names, it can include anything like the list of products, your services and price, or something else. If the number of lines is small, you can do this manually. The problem occurs when you have to do the same with hundreds of columns in Microsoft Excel or Google Sheets. To get rid of this tedious work, you can check out this step by step guide.
How to divide text into columns in Excel
To split comma-separated text in Excel, follow these steps:
- Open the spreadsheet and select all cells.
- Go to the Data tab.
- Click on the option Text in columns.
- Use the Delimited option and select a delimiter.
- Select a cell where you want to display the result.
- Save your file.
To get started, you need to open the spreadsheet in Microsoft Excel and select all the cells that contain text separated by commands. After that go to The data tab and click Text in columns option.
You will have two options – Delimited and Fixed width. You must choose the Delimited and click on the option Next button.
After that, select a delimiter. As your spreadsheet has a comma, you must select it from the list. However, it is possible to choose something else like Tab, Semicolon, Space or any other custom element. Finally, click on the Next button.
You must now choose a cell as the first column.
To return to the guide, you must delete the default value Destination and select a cell in which you want to display the output. Finally, click on the finish button.
Whichever cell or column you choose, the next one will have the last part of the text separated by commas.
For example, if-
- You have a text like this – AA, BB
- Select B1 for AA
Part BB will be recorded in cell C1. If your source text contains two commas, the very last part will be displayed in cell D1.
Now you can find the result immediately.
How to split comma separated text in Google Sheets
In this case, Google Sheets is not as smart as Microsoft Excel. Although you can split all the texts separated by commas, you cannot keep the source column. If that’s okay with you, you should follow these steps.
- Select all the cells in your worksheet.
- Go to Data> Split texts into columns.
- Change the separator if desired.
At the start, you need to select all cells containing comma-separated values. After that, expand the The data menu and select Split text into columns option.
By default, it automatically detects the separator or delimiter. If you use something unique or it doesn’t work perfectly, you can extend Separator drop-down list and choose something else as needed.
That’s it!