Splitting Text in Excel Using Flash Fill

The Text to Columns Wizard works very well for many types of data. But some data can’t be split by this wizard. For example, you can’t split data if it is not fixed width or the data doesn’t have delimiters in this technique ( Text to Columns Wizard). In this type of case, the Flash Fill feature can help you. Keep in mind that Flash Fill works successfully only when the data is consistent.

Flash Fill uses pattern recognition to separate data. For example, say you have a column which contains text in several rows.

The following image shows a worksheet with some text in a single column. Our goal is to extract the number part from each cell and put these numbers into a separate column. The Text to Columns Wizard can’t do this separation because the space delimiters aren’t consistent.

Splitting text in Excel using Flash Fill

Delimiters are not consistent means: for example, in the first row the number 20 is placed after 3 spaces, in second row 6 is placed after 2 spaces and so on. So we can’t separate numbers from this text using the “Text to Columns Wizard”. You can write an array formula to separate this type number from the text, but writing array formula is complicated.

Splitting text in Excel using Flash Fill

We shall separate numbers 20, 6, 9.5, 3.14159, 5, 3.12, 15, 7, 16, 90210 from the column.

  • Step 1: To use Flash Fill with our sample example, click on cell B1 to select it. Enter first number 20 in cell B1. Move to cell B2, and type the second number 6 in this cell. Now choose Data ➪ Data Tools ➪ Flash Fill (or press CTRL+E). You will see that the rest cells are automatically filled by Excel. Your new column will look like the following image.
Splitting text in Excel using Flash Fill

See Excel makes some incorrect guesses when we manually input the numbers in cell B1 and B2.

  • Step 2: As you see in above screenshot, Excel has identified most of the values accurately. But some wrong guesses are also there. Accuracy will increase if you provide more examples. For example, as some of your numbers have decimal values, you can input a decimal number in a new column. Delete the suggested values, enter 3.12 in cell B6, and press CTRL + E. or You can put 3.14159 in the B4 cell. This time, you will find that Excel has got them all correctly. See the following screenshot.
Splitting text in Excel using Flash Fill

After you have entered an example of a decimal number, Excel gets them all correct.

Some important points you have to remember about Flash Fill:

  • You must check your data very carefully after using Flash Fill. If you get that first few rows are correct, and you assume that Flash Fill worked correctly for all rows, then it may occur serious mistake.
  • Flash Fill increases accuracy when you provide more examples.

How to use Flash Fill to separate the First Name, Middle Name and Last Name

Rather than manually separating first, middle, or last names in new columns, you can use Flash Fill to quickly and effectively do the job. And here’s how you do it:

Step 1:

Say you have some names. For example, one name is ‘Robert De Niro’. Other names also consist of three parts like ‘Robert De Niro’. You want to separate the first name, middle name and last name in separate columns. With fill flash, you can do it easily.

Enter the first name in the column next to your data and Press Enter.

Excel 2013 new features

Step 1

Step 2:

Start typing the next name. Fill Flash will suggest the first part of all names. If it is what you want, Press Enter.

Excel 2013 new features

Step 2

Step 3:

Now we type the last part of the name in the next column. We type it in lowercase and press Enter.

Excel 2013 new features

Step 3

Step 4:

Start typing the next name. Fill flash will suggest the last name this time in lowercase. Press Enter. You can separate middle name same way.

Excel 2013 new features

Step 4

Note: Names you select must be of the same pattern. All the names will be 3 parts or 2 parts or 4 parts or ‘n’ parts. ‘n’ can be any natural number {1, 2, 3, 4, 5, 6, ……}.

Download Working File

Splitting-Text.xlsx

Names.xlsx

Read More: 

How to split text into multiple cells in Excel

How to Split Cells in Excel (The Ultimate Guide)

Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator’s Text Formula Suggestions


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply