Delimited data is data that is separated by a certain character. Comma-Separated Values (or CSV) is one of the most common forms of delimited data and looks something like this:
England,12,67,3
In this example the data would be said to be delimited using a comma as the delimiter. Here’s the same data using a hyphen (-) as the delimiter:
England-12-67-3
When you encounter data of this kind it’s common to need to split it into its individual components.
Using Flash Fill
Flash Fill was introduced in Excel 2013. It is a form of AutoFill that tries to automatically figure out which values you want it to extract.
It wouldn’t be practical to use Flash Fill with very large amounts of delimited data, but it’s an excellent tool for quickly extracting elements from it.
To split the example data using Flash Fill, you’ll first need to provide an example of what you want Flash Fill to do:
Now that you’ve provided an example, you can click in each of the cells B2, C2, D2 and E2 and press <Ctrl>+<E> or Home > Editing > Fill > Flash Fill on the Ribbon.
Flash Fill is explained in much greater depth in our free Basic Skills E-book. If Flash Fill is unfamiliar to you I’d strongly recommend it.
Using the Text to Columns tool
The Text to Columns tool is a more advanced way to split delimited data. It can be accessed from the Excel Ribbon under:
Data > Data Tools > Text to Columns
The Text to Columns tool is covered in much more depth in our Expert Skills Books and E-books.
Importing delimited files
If you are sent a file that contains delimited data, Excel will display a dialog that is very similar to the Text to Columns tool, which you can use to determine how the data will be imported into Excel.
CSV files are an exception to this, as Excel already knows how to split them. If you open a CSV file (with a .csv file extension) Excel will automatically split the data and display it like an ordinary workbook.
Using formulas
It’s possible to split delimited data using Excel’s LEFT, RIGHT and MID functions, but this is much more cumbersome than the other available choices.
LEFT, RIGHT and MID are explained in depth in our Expert Skills Books and E-books.
