5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnlineMany times, users are provided with unformatted and unclean data to work on. It can be really difficult to apply filters, sort, or even create Pivot Tables using those unstructured data.

Excel has a variety of functions and features that can be used to split those combined texts into separate columns and make data management more efficient. In this article, we will be covering 5 effective ways to split string in Excel –

Let’s look at these methods thoroughly!

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

DOWNLOAD EXCEL WORKBOOK

Method 1: Text to Column

Ever encountered a situation where you have a data set of employee full names in one column and want to split them into separate columns? No worries. Excel has an excellent ad-hoc solution to this – Text to Column.

This feature allows you to split text into separate columns using a delimiter like a comma, space, semi-column, etc. Follow the steps below to know how to split string in Excel using Text to Columns –

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

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 2: Go to Data > Text to Columns.

split string in excel

STEP 3: In the Convert Text to Columns dialog box, select Delimited > Next.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 4: Select the delimiter for the data. Here, it is Space. Check the Data Preview at the bottom and then click Next.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

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

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 6: Select Finish.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

The full name of the employees are now separated into 2 columns – First name and Last name.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

 

Method 2: LEFT, RIGHT & MID Function

In this example, we have employee data in the following format – AL56442020

  • AL – Employee’s initials
  • 5644- Employee ID
  • 2020- Year of joining

We can use LEFT, RIGHT, and MID functions to extract all three parts of this data. Follow the steps below to know how to split string in Excel –

STEP 1: Enter the LEFT function. This function is used to extract the employee’s initial i.e. first 2 characters from the text.

=LEFT(A2,2)

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 2: Enter the MID function. This function is used to extract the employee’s id i.e. 4 characters starting from the 3rd position of the text.

=MID(A2,3,4)

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 3: Enter the RIGHT function. This function is used to extract the employee’s year of joining i.e. 4 characters from the end of the text.

=RIGHT(A2,4)

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

 

Method 3: Text Functions

If you want to check where a specific text is located in the source text, it is very easy to search for the position using the FIND Formula!

In this example, we have the first name and employee id, and year of joining separated by a line breakCHAR(10) in Excel is used to get a line break in a cell. We will be using the FIND function to get the position of the line break.

Follow the steps below to learn how to split string in Excel to get them in separate columns. –

STEP 1: Enter the following formula to get the first name i.e. all characters before the 1st instance of line break.

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

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

  • Here, the FIND function will provide you with the position of the 1st line break.
  • We will subtract 1 because we do not need the line break in your result.
  • The LEFT function will extract all characters left to this position.

STEP 2: Enter the following formula to get the region i.e. all characters in between the 1st and 2nd line break.

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

split string in excel

Here, the MID function will provide the region. It has three arguments –

  • text – It is the source text located in cell A2.
  • start_num – It is the starting position for the MID function. This will be the position of 1st line break, plus 1 i.e. FIND(CHAR(10),A2) + 1.
  • num_chars – It is the number of characters that need to be extracted. This will be the difference between the position of 2nd line break and 1st line break, minus 1 i.e. FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1)-FIND(CHAR(10),A2)-1

STEP 3: Enter the following formula to get the sales amount i.e. all characters after the 3rd line break.

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

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

Here, the RIGHT function will provide the region. It has three arguments –

  • text – It is the source text located in cell A2.
  • num_chars – It is the number of characters that need to be extracted. This will be the difference between the length of the text and position of 2nd line break, plus 1 i.e. LEN(A2)-FIND(CHAR(10),A2,FIND(CHAR(10),A2)+1

 

Method 4: TEXTSPLIT Function

TEXTSPLIT function can be used to split string in Excel by a given delimiter and display the result in multiple cells. It can split the text into both columns as well as rows. It is like a formula version of the Text-to-Column feature in Excel but it is much more versatile.

This function is exclusively available for Excel 365 subscribers.

The syntax of this function is –

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

  • text – The text you want to split. Required.
  • col_delimiter – The text that marks the point where to spill the text across columns.
  • row_delimiter – The text that marks the point where to spill the text down rows. Optional.
  • ignore_empty – Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.
  • match_mode – Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.
  • pad_with – The value with which to pad the result. The default is #N/A.

In this example, let us see how to split string in Excel using the TEXTSPLIT function.

STEP 1: Enter the TEXTSPLIT formula –

=TEXTSPLIT

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 2: Enter the 1st argument i.e. text.

=TEXTSPLIT(B1,

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 3: Enter the 2nd argument i.e. col_delimiter. Here, it is left blank as we will split the text row-wise not column-wise.

=TEXTSPLIT(B1,,

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 4: Enter the 3rd argument i.e. row_delimiter. Here, it is CHAR(10).

=TEXTSPLIT(B1,,CHAR(10))

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

Repeat the same steps for the region and salary columns as well. The three rows will automatically be filled with the desired result!

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

 

Method 5: Flash Fill

Flash Fill in Excel is a feature that was introduced in Excel 2013. It is very handy as Excel predicts the rest of your inputs based on the first entry that you have placed. Once its prediction is correct and you confirm it, it will fill the rest of the rows literally in a flash!

The cool thing with Excel’s Flash Fill is there is no need to use formulas and removes manual repetitiveness, saving you heaps of time in the process!

Follow the steps below to understand how to use Flash Fill to split string in Excel –

STEP 1: Type the first name in cell B2.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

STEP 2: Select the range where you need the first names to be displayed. Here, it is B2:B27.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

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

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

All the first names will be filled automatically.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

Repeat the steps for the region and salary columns as well. This will split string in Excel.

5 Effective Ways to Split String in Excel for Better Data Management | MyExcelOnline

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin