Seperate data combined in one cell in Excel
Have you ever opened a spreadsheet where crucial information was crammed into a single column? It’s a common issue and whilst it may work initially, it can present challenges when you are looking to find, analysis, update or share information.
Difficulty in finding the information can lead to wasted time and frustration on behalf of the user. Dividing the data into more columns may help.
Common scenarios
Perhaps you’ve come across scenarios such as:
- Names – Full name in one cell when you are looking for first name and last name to be in separate columns
- Product details – Product size, name, reference all within one cell eg ‘5m Pipe X472-5’
- Addresses – City and Area being in same cell
- Text and numbers – for example product quantites eg ‘5 boxes’, measurements eg ’75cm’, price references eg ‘3 for £1’
Why separating the data into its components helps
Splitting these items up helps with:
- Calculations
- Geographical analysis
- Easier to search and filter for the information required
- Updating the information
- Consistent formatting making it easier to read
- Combining the data with other sources
Before you start – check the data format
Before splitting data you first need to understand the various formats that have been used in the cells.
- Has a pattern been followed? For example – with names has the first name consistently showing first or is the last name typically shown first
- What character has been used to split the data? For example – a space, comma, hypen, letter
- If that character is repeated then at which occurance would you like the split to occur? For example – the first occurance, the last occurance or all occurances.
- Could any of the separators serve more than one purpose in the context of the data, eg ‘T-Shirt – blue’ the first hypen is used to join together the letter and word, whereas the purpose of the second hypen is to separate the product from the colour
Often checking the first and last few rows of data can give an indication of whether the format has changed over time if the data is input in date order. Also sorting the column may help spot inconsistent formats as these formats may end up in the first or last few rows.
⚠️Warning! Strongly encourage a backup is made
Before making any for of data split, it is wise to create a backup of your spreadsheet as sometimes from just casting a eye down a column or indeed if the volume of rows is large you may not notice all the combinations which have been used in the cell. This could cause accidental overrides of content in other columns when it comes to splitting the data which will be discussed more below.
Tidy data before the split
After considering the above, the data may require some tidying up before the text can be split for instance maybe some cells with first and last name also contain a title such as Mr or Mrs whereas others don’t. You need to decide it the titles should be remain or be removed before the split.
More items to consider before the split
⚠️ Common pitfalls to avoid
Always remember to:
- Back up your data
- Check for inconsistencies in your delimiter usage
- Plan where your new columns will go
- Consider if you need to maintain the original format somewhere
Choosing your method to split the data
Excel offers several solutions to help split your data. The methods below all involve splitting text by a delimiter. A delimiter is just a letter, character or symbol such as a comma, space, hypen.
These methods include:
Text To Columns
Flashfill
Code – TEXTSPLIT, TEXTBEFORE, TEXTAFTER
Power Query
Text To Columns
Text to columns is easy and straightforward to use. Works well for:
- Simple splits with consistent delimiters. eg ‘John Smith’, ‘ Dress – Red’
Consider other methods when:
- Data contains values like “T-Shirt – Blue”. Here the data is only required to be split at the right most occurance of the “-” but this instruction can not be passed into Text To Columns.
- The split points aren’t consistent – eg Tom H Smith and Sam Jones – here without splitting at the space would result in the H and the Jones appearing in the second column as Excel sees a space and splits it there, excel does not currently understand the context.
The Excel video below explores the Text To Columns method in more detail by demonstrating:
✅ How to split text using delimiters
✅ How to use the Text to Columns function
✅ Where to safely place your new columns
✅ How to avoid accidentally overwriting data
✅ Limitations – handling multiple delimiters
More information can be found at:
Flashfill
Flashfill is helpful if you have common patterns in your data. For example to split first names and last.
More information can be found at:
Code
TEXTSPLIT – https://support.microsoft.com/en-gb/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7
- Great for splitting at the last delimiter so handles cases such as ‘T-Shirt – blue’
- Offers more control over split position
Also could consider:
TEXTBEFORE https://support.microsoft.com/en-gb/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29 or TEXTAFTER https://support.microsoft.com/en-gb/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4
Power Query
Perfect for complex or inconsistent data
- can handle multiple splitting rules
- excellent for repeatable processes
- creates a documented data transformation
- adds in the extra columns to take the result of the split so reduces the risk of data being accidentally overriden
- easy to undo any errors as can delete the step just made
Best practices
❗ Always back up your data first
❗Test your approach on a few sample rows
❗ Plan where your new column will go
❗Document your process so if the results need to be revisited the steps involved and the logic taken to get to that point.