Top 3 Ways to Split Text or Data in Microsoft Excel

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

When you encounter such a situation, Microsoft Excel offers different features that you can use to split this type of data. In this post, we cover some fixes you can try.

Using Quick Fill

The QuickFill function in Excel makes it possible to include an example of how you want the data to be split. Here’s how to do this:

Stage 1: Start the Excel file with the relevant data.

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

Stage 3: Click the next cell you want filled and click Data on the Ribbon.

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

Flash Fill has a few limitations. For example, it doesn’t output decimals correctly. It only enters digits after the decimal point. So you may need to check if your data split is done manually correctly.

Using the Limiter Function

The delimiter function is a string of characters for specifying boundaries between independent and separate regions in data streams. You can also use it to separate mathematical expressions or plain text in Excel. Some examples of delimiter functions in Excel include commas, slashes, spaces, dashes, and colons.

Here’s how to use the delimiter function:

Stage 1: Start the Excel file with the relevant data.

Step 2: Select the list of columns you need to split and click Data in the Excel Ribbon.

Stage 3: Click on Translate Text To Columns and this should open a dialog labeled Convert Text To Columns Wizard.

Step 4: Check the Restricted function from the two available options and click Next.

Step 5: On the next page of the Text to Columns Wizard, select the delimiter you need to split your data (for example, Tab, Space, etc.) and click Next. If you want to use a custom delimiter, click More and enter the delimiter in the box below.

Step 6: On the last page, choose the format of the split data and the preferred Destination.

Step 7: Click 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 segment columns with the help of delimiters. The following steps will show you how to use this method to split text or data:

Stage 1: Start Excel.

Step 2: Go to Excel Ribbon and click Data.

Stage 3: Click Get Data.

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

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

Step 6: You should see a navigation popup showing the worksheets in your workbook. Select the worksheet containing the relevant data to see a preview.

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

Step 8: Select Split Column within the Text Group and choose By Delimiter from the drop-down menu.

Step 9: In the Split Column with Separator dialog box, select the Separator type and split point, then select OK.

Step 10: Click Close on the Home tab and you will see a new worksheet showing the split data.

Exporting Data to Excel

Splitting text or data in Microsoft Excel is essential if you have to manually clean up your data to fit your preferred analysis format. Besides better presentation, you also want your data to be meaningful and readily available elsewhere.

Using any of the above methods will help minimize the time spent on data cleaning. Another way to save time and effort on data cleaning is to export data to Excel correctly.