How to Remove Text between Two Characters in Excel (3 Easy Ways)

In this tutorial, we will explain how to remove text between two characters in excel. Suppose you are working with a list of text strings where you want to remove specific text between two characters. This article will introduce you to different methods for solving this problem. To illustrate the examples of this article we will use a unique dataset.


Download Practice Workbook

We can download the practice workbook from here.


3 Ways to Remove Text between Two Characters in Excel

We will use 3 different ways to remove text between two characters in excel. To make you understand better we will use the following dataset for all three examples. The dataset contains information about the names of some employees and their delivery times & dates of products.

Suppose, we do not want the part delivery time which is in between two curly braces. Throughout this article, we will discuss how we can remove text between these two curly braces.

3 Ways to Remove Text between Two Characters in Excel


1. Remove Text between Two Characters in Excel with Find and Replace Options

First and foremost, we will use the Find and Replace options to remove text between two characters in excel. Find and Replace are perfect tools for data manipulations in excel. For instance, we will remove text between curly braces from the following image.

Remove Text between Two Characters in Excel with Find and Replace Options

So, let’s see the steps to perform this action.

STEPS:

  • To begin with, press Ctrl + H to open the ‘Find and Replace’ dialogue box.
  • In addition, in the ‘Find what’ box type an asterisk surrounded by two curly braces ({*}).
  • Furthermore, keep the ‘Replace with’ box empty.
  • Then, click on the ‘Replace All’ button.

Remove Text between Two Characters in Excel with Find and Replace Options

  • After that, excel shows us in a message box how many replacements take place by this action.
  • Now, click on OK.

  • In the end, close the ‘Find and Replace’ dialogue box. We can see from the following image that there is no text between curly braces.

Remove Text between Two Characters in Excel with Find and Replace Options

Read More: How to Remove Text After Character in Excel (3 Ways)


2. Use Flash Fill to Remove Text between Two Characters in Excel

If you are using a modern version of excel like ‘Microsoft Excel 365’ or version 2013 and later you can use the Flash Fill feature to eliminate text between two characters in excel. This method is very convenient, fast, and easy to perform.

Use Flash Fill to Remove Text between Two Characters in Excel

Let’s see the steps to use Flash Fill.

STEPS:

  • First of all, select cell C5.
  • Next, type the expected result of cell B5 in cell C5 without the instances between curly braces.
  • Then, press Enter.

  • After that, start typing the relevant value of cell B6 in cell C6. Excel will show a glimpse of the remaining cells that follow the same pattern whenever it detects a pattern in the data you’re inputting.
  • Accept the suggestions by hitting Enter.

Use Flash Fill to Remove Text between Two Characters in Excel

  • Finally, we are done! We get the text of cell (B5:B9) in cell (C5:C9) without the instances between curly braces.

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)


Similar Readings


3. Apply Excel Functions to Remove Text between Two Characters

In this method, we will use a combination of various functions to eradicate text between two characters in excel. We will use a combination of IFERROR, SUBSTITUTE, MID, LEFT, FIND, and LEN functions to execute this method.

IFERROR function delivers a normal result when a formula returns a valid result. But, the IFERROR function returns an alternative result when a formula produces an error. SUBSTITUTE function replaces particular text in a text string.

MID function in excel is used to return a certain number of characters from a text string. This function starts from the position you specify which depends on the specified number of characters.

LEFT function in excel extracts the first character or characters based on a specified number of characters

FIND function in excel is used to locate a particular character or substring inside a text string.

LEN function is used to find the length of a given text string.

Apply Functions to Remove Text between Two Characters

So, let’s see the steps to use those functions to perform this action.

STEPS:

  • Firstly, select cell C5. Insert the following formula in that cell:
=IFERROR(SUBSTITUTE(B5,MID(LEFT(B5,FIND("}",B5)),FIND("{",B5),LEN(B5)),""),B5)
  • Press Enter.
  • So, we get the value of cell B5 in cell C5 without the text between curly braces.

Apply Functions to Remove Text between Two Characters

  • After that, drag the Fill Handle tool from cell C5 to C10 to copy the formula of cell C5 in other cells. This action returns the text from the cell (C5:C9) without the text between curly braces.

🔎 How Does the Formula Work?

  • LEN(B5): This part returns the length of a text string in cell B5.
  • FIND(“{“,B5): Finds the “{“ character in the string of cell B5.
  • MID(LEFT(B5,FIND(“}”,B5)),FIND(“{“,B5),LEN(B5)): This part measures the length of string “{“ from first position.
  • SUBSTITUTE(B5,MID(LEFT(B5,FIND(“}”,B5)),FIND(“{“,B5),LEN(B5)),””): Substitutes text between the characters “{“ with empty value.
  • IFERROR(SUBSTITUTE(B5,MID(LEFT(B5,FIND(“}”,B5)),FIND(“{“,B5),LEN(B5)),””),B5): If the previous part returns an error the IFERROR function returns the value of cell B5.

Read More: How to Remove Text From Excel Cell (9 Easy Ways)


Conclusion

In conclusion, this tutorial will demonstrate how to remove text between two characters in excel. To put your skills to the test, use the practice worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try our best to reply to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo