How to Cut Text Using Formula in Excel (6 Simple Methods)

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 on 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, or all texts into different cells.

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-

• Immediately after pressing Enter, you will get the first text from 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.

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-

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-

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-

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.

Read More: How to Cut and Paste in Excel

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-

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-

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.

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.

Related Articles

• [Fixed!] Cut and Paste Not Working in Excel
• How to Cut Filtered Rows in Excel

<< Go Back to Cut in Excel | Copy Paste in Excel | Learn Excel