Sometimes we create a dataset with all the pieces of information in a single cell. But while making the final report we need to cut some text from the single cell into a new cell. This might seem difficult to you. But from now won’t it won’t be a problem for you. Today in this article, I am going to show you how to cut text using formula in Excel. Follow the methods properly to learn how to cut the **first**, **middle**, **last **text, and **all texts** into different cells.

**Table of Contents**hide

## How to Cut Text Using Formula in Excel: 6 Methods

In the following article, I have shared 6 simple and quick methods to cut text using the formula in Excel. Suppose we have a dataset of some** Employee Names** and their **Country Names **in a single cell. Now we are going to cut text using Excel formulas. Stay tuned!

### 1. Combine LEFT and FIND Functions to Cut Text in Excel

In order to cut the first text you can use the **LEFT **and **FIND functions**.

Here **the FIND function** extracts the position of a defined text. After that, **the LEFT function** returns a given number of characters from the string starting from the left side.

Thus the task is completed. Follow the steps below-

**Steps:**

- Above all, select a
**cell**. Here I have selected**cell**(**C5**) to apply the formula. - Hence, write down the formula-

`=LEFT(B5,FIND(" ",B5)-2)`

- Immediately after pressing
**Enter,**you will get the first text from the**cell**(**B5**). - Now pull the “
**fill****handle**” down to extract text from all the cells.

- Finally, we have cut the first text using a simple formula in Excel.

**Read More: How to Cut Filtered Rows in Excel**

### 2. Merge MID and SEARCH Functions to Cut Text in Excel

Sometimes you will need to cut text from the middle. Suppose we have a dataset of some **Employee’s Names **their **Countries **and **Employee codes**. Now we will cut the middle text using an easy formula.

**Steps:**

- Choose a
**cell**(**C5**) and apply the following formula-

`=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-2)`

Where,

**The SEARCH function**extracts the number of characters from a given text string.**The MID function**returns a given number of outputs starting from a given position.

- Hence, press
**Enter**and drag the “**fill****handle**” down. - In conclusion, we have the middle text as an output in the new column.

### 3. Combine RIGHT, LEN, and FIND Functions to Cut Text

In this method, we will cut the last text of a cell starting from the right. For this, we gonna combine the **RIGHT**, **LEN**, and **FIND functions **in excel.

**Steps:**

- First choose a
**cell**(**C5**) and put the formula down-

`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`

Where,

**The RIGHT function**returns the last characters from a given text string.**The FIND function**returns the position of the text.**The LEN function**extracts the length as a number of characters from a string.

- Simply, hit the
**Enter**button and then pull the “**fill****handle**” down to fill all the cells. - In summary, we have successfully cut the last text from a text string.

### 4. Merge LEFT, SEARCH, and CHAR Functions to Cut Text in Excel

While working you will find texts in a new line. Suppose we have a dataset with some **Names **and their **Countries **in a single cell but in a different line. In that case, you have to use the following formula.

**Steps:**

- Starting with we will select a
**cell**(**C5**) and put the following formula down-

`=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)`

Where,

**The SEARCH function**returns the location of one text string within a given string.**The CHAR function**returns a specific character when a valid number is used inside the string.**The LEFT function**provides the total number of characters in a given string.

- In the same fashion, hit
**Enter**and drag down the “**fill****handle**”. - Therefore, we have the output in our hands cutting text from a cell with a line break.

### 5. Apply FILTERXML Function to Cut Text in Excel

Well, in the previous methods, we cut text and extracted only the first or last text. In this method, I will show you how to cut both the first and last text into new cells.

Suppose we have a dataset With some **Names **and their **Countries**. Now we are going to cut both the **Names **and **Countries **into different cells.

**Steps:**

- Above all, choose a
**cell**(**C5**) and write the formula down-

`=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s"))`

Where,

**The FILTERXML function**extracts data from an XML file.**The TRANSPOSE function**converts a vertical range of cells to a horizontal range.

- Gently press
**Enter**and you will get both texts into two different cells.

- Now, pull the “
**fill****handle**” down to complete the action for all the cells.

- Finally, we have cut text into different parts using a simple formula.

### 6. Use Combined Formula to Cut Text and Numbers in Excel

In those previous methods, we had a total of 3 texts in a single cell. That’s why we showed you how to cut the first, middle, and last text from a cell.

Often you will have many texts or numbers in a cell. And you will need to cut them into many parts in new cells. For this I have shown a simple solution where applying a single formula will do the work for you. Let’s begin!

Suppose we have a dataset of some particular **text **and **numbers **just like the following screenshot.

Now we will cut every text or number into different cells. For that-

**Steps:**

- Choose a
**cell**(**C5**) and write the formula down-

`=TRIM(MID(SUBSTITUTE($B5," ",REPT(" ",LEN($B5))),(COLUMNS($C5:C5)-1)*LEN($B5)+1,LEN($B5)))`

Where,

**The LEN function**returns the length with numbers in a string.**The COLUMNS function**returns the count of columns.**The REPT function**is used to repeat texts a given number of times.**The SUBSTITUTE function**replaces text in a string.**The MID function**returns a given number of characters starting from a given position.**The TRIM function**removes extra spaces from a string.

- Hence, press
**Enter**.

- After that, drag the “
**fill****handle**” horizontally to fill cells with our desired result.

- Finally after dragging the “
**fill handle**” vertically we successfully cut text in Excel with a simple formula.

**Read More: How to Cut and Paste in Excel**

## Things to Remember

- While applying formulas be careful to add a proper number of
**brackets**at the end to close the strings. Otherwise, the formula won’t work.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In this article, I have tried to cover all the methods to cut text using formulas in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.