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.

How to split text or data in microsoft excel feature image

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.

How to split text or data in microsoft excel step 1

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

How to split text or data in microsoft excel step 2

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

How to split text or data in microsoft excel step 3

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.

How to split text or data in microsoft excel step 4

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.

How to split text or data in microsoft excel step 1

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

How to split text or data in microsoft excel step 18

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

How to split text or data in microsoft excel step 5

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

How to split text or data in microsoft excel step 6

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.

How to split text or data in microsoft excel step 7

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

How to split text or data in microsoft excel step 8

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

How to split text or data in microsoft excel step 9

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.

How to split text or data in microsoft excel step 1

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

How to split text or data in microsoft excel step 18

Step 3: Click on Get Data.

How to split text or data in microsoft excel step 10

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.

How to split text or data in microsoft excel step 11

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.

How to split text or data in microsoft excel step 12

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

How to split text or data in microsoft excel step 13

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

How to split text or data in microsoft excel step 15

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

How to split text or data in microsoft excel step 16

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

How to split text or data in microsoft excel step 17

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.

Last updated on 07 February, 2022

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.