Top 3 Ways to Split Text or Data in Microsoft Excel

When you import data or text into Microsoft Excel, it appears in a different format. The format that the text or data appears in depends on the source of such data or text. For example, customers’ names or employees’ addresses. By default, such data appears as a continuous string in a single cell.

If faced with such a situation, Microsoft Excel offers different features which you can use split such data. In this post, we cover some of the fixes you can try.

Using Flash Fill

The Flash Fill function on Excel makes it possible to add an example of how you want data split. Here’s how to do so:

Step 1: Launch the Excel file with the relevant data.

Step 2: On the Excel worksheet, provide an example of how you want the data or text split in the first cell.

Step 3: Click the next cell you want to be filled and, on the Ribbon, click on Data.

Step 4: In the Data Tools group, click on Flash Fill and this should split the data into the remaining cells on the selected row.

Flash Fill has a few limitations. For example, it does not extract numbers with decimals properly. It only enters digits after the decimal point. As such, you may still need to check that your data split has been done accurately manually.

Using the Delimiter Function

The delimiter function is a sequence of characters to specify boundaries between independent and separate regions in data streams. Also, you can use it to separate mathematical expressions or plain text in Excel. Some examples of delimiter functions in Excel include comma, slash, space, dash, and colon.

Here’s how to use the Delimiter function:

Step 1: Launch the Excel file with the relevant data.

Step 2: Choose the column list you need to split and click on Data in the Excel Ribbon.

Step 3: Click on Text to Columns, and this should launch a dialog box labeled Convert Text to Columns Wizard.

Step 4: Tick the Delimited function from the two options available and click on Next.

Step 5: On the next page of the Convert Text to Columns Wizard, select the delimiter you need to split your data (e.g., Tab, Space, etc.) and click on Next. If you would like to use a special delimiter, click on Other and provide the delimiter in the following box.

Step 6: On the final page, select the format of the split data and the preferred Destination.

Step 7: Click on Finish and the data should be split into multiple cells using the specified delimiter.

Using Power Query

Power Query makes it possible for an Excel user to manipulate columns into sections with the help of delimiters. The steps below will show you how to use this method to split text or data:

Step 1: Launch Excel.

Step 2: Go to the Excel Ribbon and click on Data.

Step 3: Click on Get Data.

Step 4: Select From File from the first drop-down menu and From Workbook from the second drop-down menu and this should launch File Explorer.

Step 5: Click on the Excel workbook with the relevant data and click Import.

Step 6: You should see a navigation pop-up showing the worksheets in your workbook. Select the worksheet with the relevant data to see a preview.

Step 7: Select Transform to show the Power Query Editor.

Step 8: Within the Text Group, select Split Column, and from the drop-down, select By Delimiter.

Step 9: On the Split Column by the Delimiter dialog box, select the Delimiter type and the splitting point, then select OK.

Step 10: On the Home tab, click Close and you will see a new worksheet showing the split data.

Importing Data Into Excel

Splitting text or data in Microsoft Excel is very important if you find yourself having to clean your data to fit your preferred analysis format manually. Apart from better presentation, you also want your data to make sense and make it easily usable elsewhere.

Using any of the methods above should help minimize the time spent on data cleaning. Another way to save yourself time and effort on data cleaning is by correctly importing the data into Excel.