How to separate text in excel

Our data in excel consists of different types of text. Sometimes we want to separate this text in different cells to attain a manageable data. Separation of text from a specific cell in excel can be done manually only if there is a certain amount of information. However, to separate text from a lengthy data is very difficult and time taking.

Fortunately, excel can help its users in splitting text into different cells. Most of our text is separated by delimiters such as a comma, a space and a hyphen. Excel has installed different features that will separate the text containing these delimiters and also removes these delimiters in the separated text. This article is based upon such features that would help the readers in separating text using different methods and techniques. These methods work with 2016/2019/mac/online versions.

How To Separate Text In Excel By Using Text To Column?

Text to column function has proved to be fruitful in accessing many unique features of excel. This function consists of some easy steps that help the users in separating text containing delimited in excel. These steps are mentioned below. This technique is applicable for 2016/2019/mac/online versions.

1. First, we will select the text we want to split then we will go to data tab.

2. From there we will open text to columns dialog box in the data tools.

3. Then we will click on the option of delimiters and select comma as our selected text consists of commas.

4. Next, we will select the option of destination field that will result in placing separated text in the desired cell.

5. Lastly, we will click finish.

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

How To Separate Text In Excel By Using Formulas?

Formula is an essential component in activating most of the features of Excel. We can also separate text by using formula. It is a quite technical but easy and quick method consisting of following steps. This function works with 2016/2019/mac/online versions.

1. First, we will select the cell containing merged text.

2. The text in excel worksheet consists of two components that is left and right component separated by delimiters

3. For separation of the left component of the selected text we will use formula =LEFT(A3,FIND(,,A3)-1) this will result in splitting of the left component of the text.

4. For separation of right component, the formula will be =RIGHT(A3,LEN(A3)-FIND(,,A3)) which will causes the splitting of right component of the selected text.

This procedure works with 2016/2019/mac/online versions.

How To Separate Text In Excel Using Flash Fill?

Flash fill it also a important operation of Excel that can be used to separate text in excel. The steps for the activation of flash fill feature are explained below. This function is compatible for 2016/2019/mac/online versions.

1. First, we will separate the text in a cell manually that will allow flash fill to sense a pattern for separation of text in different cells.

2. Then we will go to home tab and click on the option of flash fill or we can just press keys Crtl+E.

3. This will result in activation of flash fill and will separate the text in all the other cell.

This technique works with 2016/2019/mac/online versions.

Did you learn about how to separate text 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.