How to split a text with specific character in excel

Excel is a significant and powerful software program used for storing and analyzing data. The data in an excel worksheet is a combination of different texts and characters. The data consisting of text present in a cell is mostly separated with specific character known as delimiters. These characters include space, comma, hyphen, semicolon etc. Separation of text with these characters within a single cell makes it easy for the excel users to comprehend and interpret the data. But splitting of text with these specific characters is a difficult task.

For ease of its users excel consists of several functions that would help the excel users to split text with specific character. Splitting of text without any special characters can be done manually. But for separation of text consisting of specific characters, certain methods and techniques are required. In this article we will describe some important and easy techniques that would help the readers in understanding the concept of splitting of text with specific character in excel. These methods are compatible to 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using LEFT Function?

A text in an excel spreadsheet contains different characters that divide the text in three components i.e., left, right and middle component. This function consists of formula which helps in the splitting of left component of text with specific character. The steps for accessing this feature are as follows. This method works with 2016/2019/mac/online versions.

1. First, we will select the cell containing the text with specific character that we want to split.

2. Then we will go to formula box and enter the formula =LEFT(A3,FIND(,,A3)-1)to split the left component of the string present in the selected cell.

3. The result will be splitting of first or left component of the selected string containing specific character.

This process is applicable for 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using RIGHT Function?

For splitting the right component of a string with specific character in a cell we can use RIGHT Function. Like the LEFT Function the RIGHT Function also consists of formula that helps the excel users in splitting the end or the right component of the text. The steps of this technique are explained below. This method works with 2016/2019/mac/online versions.

1. First, we will select the substring with specific character that we want to split.

2. Then in formula box we will add formula =RIGHT(A3,LEN(A3)-FIND(,,A3,FIND(,,A3)+1)).

3. This will result in splitting of end of the text.

This process is applicable for 2016/2019/mac/online versions.

How To Split A Text With Specific Character In Excel Using MID Function?

Some of the text string consists of a middle component. For splitting the middle part of text with specific character we will follow the given steps. This function works with 2016/2019/mac/online versions.

1. First, we will select the cell containing the text with specific character

2. Then for the separation of middle segment of text we will apply the formula=MID(A3, FIND(,,A3) + 1, FIND(,,A3,FIND(,,A3)+1) - FIND(,,A3) - 1).

3. This will cause the separation of middle part of the text.

This method is applicable for 2016/2019/mac/online versions.

Did you learn about how to split a text with specific character in excel using different methods? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.