Dealing with unformatted and unclean data in spreadsheets can be challenging, especially when trying to apply filters, sort, or create Pivot Tables. Thankfully, Microsoft Excel offers various functions and features to help split combined texts into separate columns, enhancing data management efficiency.

In this article, we'll explore the top 5 methods for splitting strings in Excel, complete with free step-by-step examples and a practice workbook.

Let's delve into these methods in detail!

Download the Excel Workbook below to follow along and understand how to split strings in Excel –

Download excel workbookSplit-String-in-Excel.xlsx

Table of Contents

Method 1: Text to Column

Have you ever needed to split a column of employee full names into separate columns? Excel offers an excellent solution with the Text to Column feature.

This feature enables you to split text into separate columns using a delimiter such as a comma, space, or semicolon. Here’s how to use Text to Columns to split strings in Excel:

STEP 1: Select the range containing the full names of the employees.

STEP 2: Navigate to Data > Text to Columns.

STEP 3: In the Convert Text to Columns dialog box, choose Delimited and click Next.

STEP 4: Choose the delimiter for the data. In this case, it's Space. Check the Data Preview at the bottom and then click Next.

STEP 5: Select cell $B$2 as the destination and click Next.

STEP 6: Click Finish.

Now, the full names of the employees are split into two columns: First name and Last name.

Method 2: LEFT, RIGHT & MID Function

For data in the format AL56442020, where:

• AL represents the employee's initials,
• 5644 is the employee ID,
• 2020 is the year of joining,

we can use the LEFT, RIGHT, and MID functions to extract these parts. Here's how to split strings in Excel using these functions:

STEP 1: Use the LEFT function to extract the employee's initials (the first 2 characters).

=LEFT(A2,2)

STEP 2: Use the MID function to extract the employee's ID (4 characters starting from the 3rd position).

=MID(A2,3,4)

STEP 3: Use the RIGHT function to extract the year of joining (the last 4 characters).

=RIGHT(A2,4)

Method 3: Text Functions

To locate a specific text within a source text, you can use the FIND Formula. In this example, we have data with the first name, employee ID, and year of joining separated by line breaks. CHAR(10) in Excel represents a line break. We'll use the FIND function to determine the position of these line breaks.

Here's how to split strings in Excel to separate them into columns:

STEP 1: Use the following formula to extract the first name (all characters before the first line break).

=LEFT(A2,FIND(CHAR(10),A2)-1)

• The FIND function locates the position of the first line break.
• Subtracting 1 excludes the line break from the result.
• The LEFT function extracts all characters up to this position.

STEP 2: Use the following formula to extract the region (characters between the first and second line breaks).

=MID(A2,FIND(CHAR(10),A2) 1,FIND(CHAR(10),A2,FIND(CHAR(10),A2) 1)-FIND(CHAR(10),A2)-1)

The MID function extracts the region with these arguments:

• text – The source text in cell A2.
• start_num – The starting position (position of the first line break plus 1).
• num_chars – The number of characters to extract (difference between the second and first line break positions, minus 1).

STEP 3: Use the following formula to extract the sales amount (characters after the second line break).

=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2,FIND(CHAR(10),A2) 1))

The RIGHT function extracts the sales amount with these arguments:

• text – The source text in cell A2.
• num_chars – The number of characters to extract (difference between the text length and the position of the second line break, plus 1).

Method 4: TEXTSPLIT Function

The TEXTSPLIT function allows you to split strings in Excel using a specified delimiter and display the results in multiple cells, either in columns or rows. It's a more versatile version of the Text-to-Column feature, available exclusively to Excel 365 subscribers.

The function's syntax is:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• text – The text to split. Required.
• col_delimiter – The delimiter for splitting across columns.
• row_delimiter – The delimiter for splitting down rows. Optional.
• ignore_empty – Set to TRUE to ignore consecutive delimiters. Defaults to FALSE. Optional.
• match_mode – Set to 1 for case-insensitive matching. Defaults to 0 for case-sensitive matching. Optional.
• pad_with – The value to pad the result with. Defaults to #N/A.

Here's how to use the TEXTSPLIT function to split strings in Excel:

STEP 1: Enter the TEXTSPLIT formula.

=TEXTSPLIT

STEP 2: Enter the first argument, text.

=TEXTSPLIT(B1,

STEP 3: Enter the second argument, col_delimiter. Leave it blank for row-wise splitting.

=TEXTSPLIT(B1,,

STEP 4: Enter the third argument, row_delimiter. Use CHAR(10) for line breaks.

=TEXTSPLIT(B1,,CHAR(10))

Repeat these steps for the region and salary columns. The rows will automatically fill with the desired results!

Method 5: Flash Fill

Introduced in Excel 2013, Flash Fill is a handy feature that predicts and fills data based on your initial input. Once confirmed, it populates the rest of the rows quickly, eliminating the need for formulas and reducing manual effort.

Here's how to use Flash Fill to split strings in Excel:

STEP 1: Type the first name in cell B2.

STEP 2: Select the range where you want the first names to appear (B2:B27).

STEP 3: Go to Home > Fill > Flash Fill.

The first names will be automatically filled.

Repeat these steps for the region and salary columns to split the strings in Excel.

The above is the detailed content of 5 Best Ways to Split String in Excel (Free Examples). For more information, please follow other related articles on the PHP Chinese website!