Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

How to split delimited data in Excel

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.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

Separating text and numbers in Excel

Separating text and numbers in Excel

This article shows three different ways of separating text and numbers in Excel. You’ll see how to do this using Flash Fill, Text to Columns and formulas.

Leave a Reply

Your email address will not be published.

fifteen + seven =