How to split text into multiple cells in Excel

When you import data from another source, it may happen that multiple values have been imported into a single column. Following image shows an example of this type of import incident.

Splitting Text in Excel

Imported in one column rather than in multiple columns.

Tip: Your default font may not support fixed-width font to display data. Courier New is a fixed-width supported font. I have used Courier New font to generate fixed-width data like the above image.

If the length of the text is the same as our example, you can split the text in your own way. You can use formulas that will extract the data to separate columns. You can use LEFT, RIGHT, and MID formula to split the text character wise.
We shall discuss here two easy ways. These ways are non-formula methods: Text to Columns and Flash Fill.

Using Text to Columns to split text in Excel

 The Text to Columns command can split the text into their component parts.
  • The first step to split data in this method is to see whether there are enough empty columns to the right to accommodate the extracted data.
  • Now select the range of data which you want to split. Choose Data ➪ Data Tools ➪ Text to Columns. Excel displays the Convert Text to Columns Wizard. Convert Text to Columns Wizard consists of three dialog boxes that will walk you through the processes to convert a single column of data into multiple columns.
Using “Text to Columns” to split text in Excel

The first dialog box in the Convert Text to Columns Wizard.

Delimited: If your data is delimited by commas or spaces, tabs or slashes, or other characters, you should use this option.

Fixed Width: If each component of your text occupies exactly the same number of characters.

In our example, we can use both options as our data is delimited by spaces and our data components are of equal sizes. We are choosing Delimited as it is more convenient for us and click Next button of the Wizard.

If your text has delimiting character or characters, specify the characters in the second dialog box of the wizard. You’ll see a preview of the result.

Using “Text to Columns” to split text in Excel

The second dialog box in the Convert Text to Columns Wizard.

If you’re working with fixed-width data, you will work directly in the Preview window. Here you can create a new brake line, you can delete break line, or you can move a broken line.

Using “Text to Columns” to split text in Excel

The second dialog box of Text to Columns wizard box. This shows when you selected the Fixed Width option in the first dialog box.

When you’re satisfied with the column breaks, click Next to move to Step 3. In this step, you can click a column in the preview window and specify general formatting for the column. Click Finish, and Excel splits the data as specified.

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.

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 a 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:

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.

Step 1:

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, ……}.

Happy Excelling 🙂

Download Working File

Download the working file from the link below:

Splitting-Text.xlsx

Splitting-Text.xlsx

Names.xlsx

Read More…

Using Text to Columns to split text in Excel

How to Split Cells in Excel (The Ultimate Guide)

 Split one column into multiple columns in Excel [Text to Columns]


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