Excel - Splitting cells

If you have an excel database and want to split cells there are a couple of ways to do it.

With a Delimiter (eg comma)

If the cells you want to split have a delimiter (eg where you want to separate them is a comma (,) or other delimiter eg | or a tab then you do it this way -

• Select the cells you want to split
• Add columns next to the cells (if there is more than one split make sure you put enough new columns as otherwise any data in the following column will be overwritten)
• Select - Data | Text to Columns
• Choose "Delimited" | Next
• Define the type of delimiter
• If you need to define your break further press Next where you get some options, otherwise
• Finish

This will split the column where the delimiter is found.

Without a Delimiter

If your column is just a string of words and there's no way to define where you want to split then you do it this way -

• Select the cells you want to split
• Add columns next to the cells (if there is more than one split make sure you put enough new columns as otherwise any data in the following column will be overwritten)
• Select - Data | Text to Columns
• Choose "Fixed Width" | Next
• Here lines with arrows signfiy a column break - to CREATE a new break click at the desired position and to DELETE any current or wrong breaks double click on the line. To MOVE a break click and drag it
• It is often easier just to drag any unwanted breaks to the left or right across the screen and off and that will delete them rather than a lot of double clicking
• If you need to define your break further press Next where you get some options, otherwise
• Finish

If your breaks are at different places in your column unfortunately you have to do each row one at a time.

Breaking down a business name, address, suburb

If you have a database that has the business name, address and suburb all just in one column with no delimiter you can use Find and Replace to remove say the suburb and in the new suburb column type in the top column and drag down to copy.

Then you can split the name and address by either copy/paste or the above fixed width delimiter above

If you do this way it is probably more time consuming and when you do find and replace you MUST ensure you note any business names that use the suburb word(s) in their name as this will delete it from there too.

The best way is probably the above Fixed Width Delimiter method although both ways is time consuming!