“That should be easier,” you may have thought while manually splitting a large number of cells in Microsoft Excel. First and last names, street names and house numbers.
Microsoft Excel, it often seems like a program that you will never be able to fully master. After tens of hours of training at the university of applied sciences, I have to agree with everyone who thinks so. No matter how well you can handle functions and pie charts, you will always encounter a problem that you cannot solve immediately. Don’t give up is the message, and look for an answer to your questions. We will help you split up cells and save you a lot of time.
How to split cells in Excel?
You can split a cell in Excel by selecting it and then converting the text to columns. Then you choose the correct separator and you can complete the split.
Step 1: Select cells and prepare your Excel worksheet
We start by studying the data we have. What exactly do we want to achieve? Let’s take a collection of addresses as an example. The goal is to separate the street, house number, zip code and town and place each in a separate column. At the moment this is still one column containing, for example, “Clickxstraat 368, 2000 Antwerp”. Our end result will therefore be spread over four columns. We first make sure that we have that space. This can be done by pasting your data into an empty Excel file, or by inserting empty columns on the right. You do this by going to the column next to your data and in the tab ‘Start’ and the submenu ‘Cells’, click on ‘Insert’.
Step 2: Text to columns
Now select the data you want to split and navigate to the ‘Data’ tab where you click on ‘Text to Columns’ in the ‘Data Tools’ submenu. You can now choose to separate your data based on spaces between the information (as is the case in our example) or by drawing lines that indicate the division (only useful if all your data looks exactly the same). We choose ‘Divorced’ here.
Step 3: Select separators
After clicking “next”, we will be asked to indicate which characters we consider delimiters. In the case of our example (Clickxstraat 368, 2000 Antwerp) these are different spaces and a comma. Since there is a comma and space next to each other, in this case we leave ‘Consider duplicate delimiters as one’ checked. At the bottom you will already see an example of the final result. If that looks good, click next.
Step 4: Completing the split
In this last step, you will select per column what kind of data it contains. In our example, there is no data that should be given a special type, so ‘Standard’ works fine in this case. For example, if you have data containing a date, it can help to convert those fields to the type of date. Under the ‘Advanced’ button you can choose what kind of decimal separator you want and whether numbers over 1000 should be split with a period or comma. Finally click on ‘Finish’ and enjoy the result!