To quickly split the text in Excel into multiple columns, three methods can be used: 1. The text-to-column function is suitable for data with fixed separators. After selecting a column, click "Text to Column" in the "Data" tab and select the separator; 2. The formula is suitable for flexible processing of irregular data, such as using LEFT, RIGHT, FIND and other functions to extract the mailbox username or domain name; 3. Quick fill is suitable for data with inconsistent formats. After manually entering the first few lines, Excel will automatically recognize the pattern and complete the filling. The three methods have their own advantages and can be selected according to the characteristics of the data.

If you've ever looked at a column in Excel full of combined data—like names and addresses stuck together—and thought, “This would be easier to work with if it were split into separate columns,” you're not alone. The good news is that Excel has some straightforward tools to help you split text into columns quickly, whether it's separated by commas, spaces, tabs, or other delimiters.

Split Text into Columns Using the Text to Columns Feature

This is the most common and user-friendly way to split text in Excel. It works best when your data follows a consistent pattern, like values separated by commas, spaces, or tabs.

Here's how to do it:

• Select the column containing the text you want to split.
• Go to the Data tab on the ribbon.
• Click on Text to Columns (you'll find it in the Data Tools group).
• Choose Delimited if your data uses characters like commas or spaces to separate values.
• Click Next, then choose which delimiter(s) apply (common ones are space, comma, tab, or semicolon).
• Preview how your data will look in the bottom window.
• Click Finish to apply the split.

For example, if you have a cell with "John,Doe" and you split by comma, "John" will go in one column and "Doe" in the next.

Use Formulas When You Need More Control

If your data isn't consistently formatted or you need more flexibility, formulas can help. Two commonly used functions for splitting text are LEFT , RIGHT , MID , and FIND .

Let's say you have an email address like "john.doe@example.com" and you want to extract just the username before the @ symbol.

You could use:

 =LEFT(A1,FIND("@",A1)-1)

This formula finds the position of "@" and returns all characters to the left of it.

To get the domain part after "@", you might use:

 =RIGHT(A1,LEN(A1)-FIND("@",A1))

These formulas aren't as fast as Text to Columns, but they update automatically if your source data changes, which can be a big plus.

Try Flash Fill for Complex or Mixed Formats

Flash Fill is a smart tool in Excel that detects patterns and fills in data accordingly. It's especially handy when your data doesn't follow a strict format.

For instance, if you have a list of full names like "John Doe" and you want to split them into first and last names:

• Manually type "John" in the adjacent column.
• Start typing "Jane" below if the next name is "Jane Smith".
• As soon as Excel detects the pattern, it will auto-fill the rest of the column.

You can also trigger it manually by going to the Data tab and clicking Flash Fill .

It's less precise than formulas and Text to Columns, but great for quick cleanups when formatting varies slightly.

Depending on your data and how much control you need, any of these methods can work well. Text to Columns is usually the fastest for structured data, formulas give you dynamic updates, and Flash Fill is perfect for irregular formats. Pick the one that fits your situation best — and don't forget to double-check the results, especially if your dataset is large or messy.

Basically that's it.

The above is the detailed content of How to split text into columns in Excel. For more information, please follow other related articles on the PHP Chinese website!