Mursalin Ibne Salehin
In this article, we will learn to replace text between two characters in Excel. Sometimes, we import datasets from other sources. Those datasets can contain unnecessary text between two characters. Today, we will show 3 easy methods to replace text quickly. So, without further ado, let’s start the discussion.
Download Practice Book
Download the practice book here.
3 Easy Ways to Replace Text between Two Characters in Excel
To explain the methods, we will use a dataset that contains information about the Name, IDs, and Departments of some employees. We will try to replace the IDs between the square brackets with some texts.
1. Replace Text between Two Characters with Find and Replace Feature in Excel
The easiest way to replace text between two characters in Excel is to use the Find and Replace feature. It is easy to use and can become very handy in large datasets. Before starting the main process, take a look at the dataset again. Here, we are going to replace the ID numbers with the text ‘Not Available’.
Let’s pay attention to the steps below to learn the method.
STEPS:
- In the beginning, go to the Home tab and select ‘Find & Select’. A drop-down menu will occur.
- Select Replace from the drop-down menu. It will open the ‘Find and Replace’ dialog box.
- Alternatively, you can press Ctrl + H on the keyboard to open it.
- Secondly, in the ‘Find what’ field, type an asterisk surrounded by the square brackets [*].
- After that, you need to type the text in the ‘Replace with’ field. Here, we have typed [Not Available].
- Then, select Replace All.
Note: To replace texts in a single cell, click on the ‘Replace’ button.
- After selecting Replace All, a message box will appear to show the number of replacements.
- Click OK to proceed.
- Finally, close the ‘Find and Replace’ dialog box. You will see the ID numbers between the square brackets are replaced.
Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
2. Use Excel Flash Fill Option for Replacing Text between Two Characters
You can also use the Flash Fill option for replacing text between two characters. The Flash Fill option is available in Excel 2013 and later versions. So, you can’t use this method in the earlier versions. Here, we will use the same dataset. But, we will need another column to apply the Flash Fill.
Let’s follow the steps below to know more.
STEPS:
- First of all, select Cell E5 and type the desired result. We want to replace 101 with X. So, we have typed Adam [X] (Accounting).
- In the second step, select the cells where you want to apply the Flash Fill.
- After that, go to the Home tab and select Fill. A drop-down menu will occur.
- Select Flash Fill from the drop-down menu.
- In the end, you will see results like the picture below.
Read More: How to Replace Text after Specific Character in Excel (3 Methods)
Similar Readings
- Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
- Excel VBA to Find and Replace Text in a Column (2 Examples)
- Excel VBA: How to Find and Replace Text in Word Document
- Excel VBA: Open Word Document and Replace Text (6 Examples)
3. Insert Excel Formula to Replace Text between Two Characters
In the last method, we will use different functions to create a formula. And with that formula, we will replace text between two characters easily. Here, we will use a combination of IFERROR, SUBSTITUTE, MID, LEFT, FIND, and LEN functions to build the formula. These functions perform different tasks.
To learn the process, follow the steps below.
STEPS:
- In the first place, select Cell E5.
- Secondly, type the formula:
=IFERROR(SUBSTITUTE(B5,MID(LEFT(B5,FIND("]",B5)),FIND("[",B5),LEN(B5)),"[X]"),B5)- After that, press Enter to see the result.
- In the last step, drag the Fill Handle down to copy the formula.
🔎 How Does the Formula Work?
- LEN(B5)
The LEN function measures the length of a given text string. This part of the formula returns the length of the text string in Cell B5.
- FIND(“[“,B5)
Here, it searches the “[“ character in the string of Cell B5. As the FIND function is used to search a particular text or substring in a string.
- MID(LEFT(B5,FIND(“]”,B5)),FIND(“[“,B5),LEN(B5))
We know that the MID function extracts characters from the middle of a text string. To do so, you need to mention the starting position and length of the string. And the LEFT function takes out a specified number of characters from the start of a text string.
In this case, the formula measures the length of the string “[“ from the first position.
- SUBSTITUTE(B5,MID(LEFT(B5,FIND(“]”,B5)),FIND(“[“,B5),LEN(B5)),”[X]”)
It is the most important part. The SUBSTITUTE function replaces a specific text with a new one in a text string. So, It substitutes text between the square brackets with [X]. You need to write the replacement in the last argument inside the double quotation.
- IFERROR(SUBSTITUTE(B5,MID(LEFT(B5,FIND(“]”,B5)),FIND(“[“,B5),LEN(B5)),”[X]”),B5)
The IFERROR function ignores the errors and produces a custom result. Generally, it shows the normal result when an error occurs. Here, if the formula returns an error, then the IFERROR function delivers the value of Cell B5.
Read More: How to Replace Text in Excel Formula (7 Easy Ways)
Things to Remember
There are certain things you need to remember when you are trying to replace text between two characters.
- In Method-1, be careful with the ‘Find and Replace’ dialog box. If you want to replace texts in a single cell, then don’t use ‘Replace All’. Use ‘Replace’ instead.
- You can’t use Method-2 in Excel 2010 and earlier versions.
- Be extra careful when you are using Method-3. Because the formula is long and any mistake will return an incorrect result.
Conclusion
In this article, we have demonstrated 3 easy methods to Replace Text between two characters in Excel. Here, we have used practical datasets to explain the process. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- Excel VBA to Replace Blank Cells with Text (3 Examples)
- How to Replace Text with Blank Cell in Excel (5 Simple Methods)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
- How to Replace Text in Selected Cells in Excel (4 Simple Methods)
- Excel Formula to Replace Text with Number (5 Examples)
- Excel VBA: Replace Character in String by Position (4 Effective Ways)
- How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
