7 Easy Ways to Replace Text with Blank Cell in Excel

In this article, I’ll show you three excellent methods on how to replace text with blank cell in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Replace Text with Blank Cell in Excel with These 7 Useful Techniques

Here in this article, we will learn how to replace text with blank cells in Excel using different approaches. To be exact, I’ve provided a total of 7 methods down below.

We considered sales data as our dataset which contains the product of the different companies and their different branches’ sales amounts. It consists of four columns as Company, Product, Branch, and, Sales.

Firstly we will employ Replace option from the top ribbon to replace text with blank cells in Excel. Hence, you may easily locate and replace particular text or values within a worksheet or a chosen range of cells in Excel by using the Replace option. To quickly and easily discover and replace certain text or values within a worksheet or chosen range of cells, without having to manually search and replace, you can utilize the Replace option while working with big and complex data sets.

Let’s explore this approach step by step.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells you want to replace with blank.

• Go to the Home tab > Find and Select option.
• Choose the Replace option.

• Write the text you want to replace in the Find What option.
• Keep blank in the Replace With option and choose the Replace All option.

• Finally, you will see the cells you selected are blank.

Thus, you can replace text with blank cell in Excel.

Now we will use  Excel Formula to replace text with blank cell in Excel. In Excel, the empty formula that produces an empty string is =”” (i.e. “”). A cell’s contents can frequently be cleared using this formula without changing the cell’s formatting or other attributes. It’s vital to understand that the formula “=””” just clears the contents of the cell; it does not destroy the cell itself. You can either use the keyboard shortcut Ctrl + – or right-click on the chosen cells and pick “Delete” from the context menu to remove a cell or group of cells (minus).

Here I demonstrated the process step by step.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cells you want to replace with blank.
• Then go to the formula bar.
• Write the following formula:

• Finally hit the Ctrl+Enter button to see the results.

Accordingly, you can replace text with blank cell in Excel.

Now, I will use REPLACE function in this approach. You can substitute new characters for specific characters in a string using Excel’s REPLACE function. The function’s syntax is as follows:

Unlike having to manually search and replace text, the method is helpful when you wish to replace particular characters within a string or cell. Working with huge datasets can result in significant time and effort savings.

Here, I gave a detailed explanation of the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• First, select cell F6.
• Insert the following formula and Press Enter key:

• Later drag the fill handle to see the output for the entire dataset.
• Then you will see the cells your selected cells converted into blank.

Hence, you will be able to replace text with blank cell in Excel.

We applied the SUBSTITUTE function in this method. Excel’s SUBSTITUTE text function enables you to substitute fresh text for a specific portion of a string. The function’s syntax is as follows:

This formula simply converts the cell’s text into blank in one click. Working with huge datasets can result in significant time and effort savings.

I will show the steps of the technique one by one.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cell F6
• Write down the following formula:

• Press Enter and drag the fill handle to see the result.

That’s how you can replace text with blank cells in Excel.

Now We will apply the Go To Special feature in this method. You can easily choose particular cells or ranges of cells based on parameters using Excel’s “Go To Special” option. However, this choice is accessible via the “Home” tab’s “Editing” group’s “Find & Select” menu bar. Moreover, with the “Go to Special” option, you may quickly and easily choose and work with the precise cells or ranges of cells that you require while working with big and complex data sets.

I will show the steps of the technique one by one.

⬇️⬇️ STEPS ⬇️⬇️

• Select Home tab > Find and Select feature > Go to Special wizard or simply use the keyboard shortcut Ctrl+G.

• Choose Formulas and select the option OK.

• Consequently, go to the Formula box and insert the following formula:

• Finally, then click Ctrl+Enter to see the results.

Hence, you will be able to replace text with blank cell in Excel.

Here we will use the Keyboard Shortcut option to solve this problem. On a keyboard, press the Delete key to remove the selected text or the character that is directly adjacent to the cursor. Near the Insert key in the upper right corner of the keyboard is where you’ll generally find it. On a keyboard, press the Delete key to remove the selected text or the character that is directly adjacent to the curso

Here, I gave a detailed explanation of the procedure.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells you want to substitute with blank.

• Press the Delete key on the keyboard.

Thus, you can replace text with blank cell in Excel.

Finally, we used VBA macro in this technique. Excel the VBA macros are a collection of instructions that can be used to automate various operations, including data preparation, chart creation, and calculation. However, examples of VBA macro functionality include the creation of a new worksheet with pre-populated data taken from another worksheet depending on certain criteria and automatically formatting a set of cells each time the workbook is accessed. The steps are given in proper sequence below

⬇️⬇️ STEPS ⬇️⬇️

• Take the cursor to the sheet name and click right.
• Afterward, select the option View code.

• Insert the following VBA code:

Sub Texttoblank()
Range("C6:C10").ClearContents
End Sub

• Run the code to see the output.

Accordingly, you may replace text with blank cell in Excel.

📄 Important Notes

🖊️  Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.

🖊️  Moreover, always look for ways that are easy to use in different situations.

🖊️  Finally, every time you try to use shortcuts on the keyboard.

📝 Takeaways from This Article

📌  You can replace text with blank cells in Excel using Replace option.

📌   Moreover, You can replace text with blank cells in Excel using REPLACE function.

📌  In addition, You can replace text with blank cells in Excel using the SUBSTITUTE option.

📌  Finally, you can replace text with blank cells in Excel using VBA macro.

Conclusion

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to replace text with blank cell in Excel. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook that I’ve shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.