Split Text at Specified Character

Welcome to this tutorial on splitting text at a specific character using Microsoft Excel. In this tutorial, we will explore four different methods to split text: Text to Columns, Flash Fill, Formulas, and Power Query. Each method has its own unique advantages, and we will cover step-by-step instructions for each exercise. So, let’s dive right in!

Video

Walkthrough

To demonstrate the four different methods, Text to Columns, Flash Fill, Formulas, and Power Query, we’ll take them one exercise at a time.

Exercise 1: Splitting with Text to Columns

Text to Columns is a feature that has been available in Excel for many years. It is suitable when you don’t need to retain the original column. Consider the following worksheet:

We’d like to split each email address at the @ character. So, we following these steps:

1. Select the range of cells containing the text you want to split.

2. Go to the Data tab and click on Text to Columns.

3. In the Text to Columns wizard, choose “Delimited” since we are splitting at a specific character.

4. Click Next and specify the desired delimiter (in this case, the @ symbol).

5. Click Next and choose the desired format for the columns (General is usually fine).

6. Click Finish and bam:

Notice that the original data is gone, replaced by the split columns. What if you wanted to retain the original data column? Well, for that, we can use Flash Fill

Exercise 2: Splitting with Flash Fill

Flash Fill is a handy tool for splitting static values or lists that are not dynamic or changing. We have the same original list:

To use Flash Fill to split the list:

1. In the adjacent empty column to the right, enter a sample of what you want.

2. Go to the Data tab and click on Flash Fill.

3. Excel will automatically fill in the split values based on the example you provided.

4. Repeat this process for other split values by entering them as examples in subsequent cells.

And this is great for one-time projects where the values don’t change. But, what if you want something more dynamic, that will update if the cell values update? Well, using formulas may be a nice approach.

Exercise 3: Splitting with Formulas

Formulas provide a more dynamic solution for splitting text at a specific character. For this exercise, we will use two functions: TEXTBEFORE and TEXTAFTER.

Same basic list as before:

Follow these steps:

1. In an empty column, enter the formula:

=TEXTBEFORE(A1,"@")

2. Press Enter and Excel will display the split text.

3. Drag the fill handle down to apply the formula to other cells.

4. Repeat the process using:

=TEXTAFTER(A1,"@")

5. Drag the fill handle down to apply the formula to the remaining cells.

Formulas are a great approach when the data is in Excel. But, what if we want to import data from another source and split the column on its way into Excel? For that, we can turn to Power Query.

Exercise 4: Splitting with Power Query

Power Query provides a powerful and flexible way to split text. Just head to the Data > Get Data command and select the appropriate data source (external csv, table, workbook, database, etc). Once you’ve identified the data source, you’ll see a preview of the data in the Power Query editor:

3. In the Power Query Editor, select the column you want to split.

3a. If you do not need to retain the original column, you can select Home > Split Column > By Delimiter and identify the delimiter (@).

3b. If you want to leave the original column there, click Add Column > Extract > Text Before Delimiter and specify the @ delimiter.

Then again but select “Extract Text after Delimiter” this time.

4. Home > Close & Load To, and select Table in a new or existing worksheet and bam:

And that is how we can use Power Query to split text columns at a specific character.

Conclusion

Congratulations! You have learned four different methods to split text at a specific character in Microsoft Excel. Whether you prefer Text to Columns, Flash Fill, Formulas, or Power Query, you now have the tools to accomplish this task efficiently. Feel free to explore these methods further and apply them to your own datasets. If you have any suggestions, alternatives, questions, or improvements … please post a comment!

Sample file

Frequently Asked Questions

Q: Can I split text into more than two columns?

Yes, you can split text into multiple columns using any of the methods described in this tutorial. Simply repeat the steps for each additional column.

Q: What is the purpose of splitting text at a specific character in Excel?

A: Splitting text at a specific character can be useful for various tasks such as separating email addresses, extracting data from delimited lists, or organizing information into distinct categories.

Q: Which method should I use to split text in Excel?

A: Excel provides multiple methods for splitting text, including Text to Columns, Flash Fill, Formulas, and Power Query. The choice of method depends on the specific requirements of your task and the level of automation you desire.

Q: Will splitting text affect the original data in Excel?

A: The Text to Columns method replaces the original data with the split columns, while the Flash Fill method creates a new column with the split values. Formulas and Power Query also allow you to retain the original data and create new columns for the split values.

Q: Can I split text in Excel if the data is from an external source?

A: Yes, you can use Power Query to import data from an external source and split the text column during the import process. Power Query provides a flexible approach and allows you to apply transformations to the data before loading it into Excel.