How to text split in Excel

In Excel, the term splitting cells refers to dividing a cell's content into two or more distinct cells. When huge datasets are imported into Excel from outside sources, splitting cells is frequently necessary. In these circumstances, it is necessary to construct different columns for related data values.

You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data.

Instructions about functions in this article are compatible with 2016/2019/mac/online.

The Delimited Function

How can a comma separated list be converted into cells in a column? This can be done using a Delimited function (commas or spaces) and can be useful when importing data from a word document into excel.

1. Select the cell contains the text you want to split.

2. Click on Data option in the Main Menu Text to Columns. There is a drop down menu for ‘Text to Columns.’ Select ‘Text to Columns’.

3. In the pop-up Wizard, there a total of three steps. Make sure you select the Delimited and then press Next.

4. Select the delimiter you want to remove in order to form a list in columns. In our case, it is commas.

5. Choose the destination in your worksheet which is where you want the split data to appear. You can keep it as it is or change. In the column data format, you can choose general or text. The data preview can help you see the how the data will be displayed and if you need to make changes.

6. Press finish for result.

The LEFT Function

It can return an array of string values for a specified number. You can then use the LEFT function to extract the first component of the text string. Consecutively, other functions like FIND can be put in the argument for more complicated results. Let’s discuss how you can extract some letters from the left of a cell.

1. Type =LEFT in the target cell.

2. Double-click on the LEFT function. The argument needs the cell you want to extract from. The second entry is the number of characters you wish to extract. Here, I want to extract first three characters from the cell A9.

3. Press enter to see result.

4. Similarly, the RIGHT function can be used to extract the characters from the right.

5. Press enter for result.

The MID and SEARCH Function

1. Let’s suppose we need to extract first names from a list of names.

2. The formula we will use here will be =MID(A1,1,SEARCH(“ “,A1)-1). The space character () is located in the original string using the SEARCH function, which returns its position, from which we deduct 1 to prevent trailing spaces. The first name is then retrieved by returning a substring starting with the first character and ending with the character before the space using the MID function. Double-click on the functions for use.

3. Press enter to see result.

4. To duplicate this formula in the remaining column's cells, just click twice on the fill handle at the bottom-right corner of each cell or hold and drag it down. Or you can use Ctrl+C (copy) and Ctrl+V to copy formulas in the column below. The result is satisfactory.

5. To extract last names, trim function would also be used in order to remove the space at the beginning.

Did you learn about how to split text in WPS Excel? 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, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.