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.

Overview of how to replace text with blank cell in Excel.


📁 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.

Sample Dataset of Sales data 2022

Method

1. Using Find & Select Feature

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.

Selecting cells to replace

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

Selecting 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.

Choosing the option ‘Replace All’

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

Final output

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

Method

2. Apply Excel Formula to Replace Value with Blank 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:

<span style="font-size: 14pt; color: #000000;"><strong>=""</strong></span>

Inserting formula in the formula bar

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

Final results

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

Method

3. Using REPLACE Function

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:

REPLACE(old_text, start_num, num_chars, new_text)

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:

<span style="font-size: 14pt; color: #000000;"><strong>=REPLACE(C6,1,5,"")</strong></span>

Inserting formula with REPLACE function

  • 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.

Final output

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

Method

4. Using SUBSTITUTE Function

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:

SUBSTITUTE(text, old_text, new_text, [instance_num])

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:

<span style="font-size: 14pt; color: #000000;"><strong>=SUBSTITUTE(C6,"Phone","")</strong></span>

Inserting formula with SUBSTITUTE function

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

Final Output

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

Method

5. Using Go To Special Feature

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.

Selecting Go to Special feature

  • Choose Formulas and select the option OK.

Selecting the option formula

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

<span style="color: #000000;"><strong><span style="font-size: 14pt;">=""</span></strong></span>

Inserting formula to replace with blank cells

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

Final results

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

Method

6. Keyboard Shortcut to Make Blank Cells 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.

Selecting cells to replace with blank cells

  • Press the Delete key on the keyboard.

Final output

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

Method

7. Applying VBA Macro to Replace Text with Blank Cell

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.

Selecting the option View Code

  • Insert the following VBA code:
Sub Texttoblank()
Range("C6:C10").ClearContents
End Sub

Inserting VBA code

  • Run the code to see the output.

Final results

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.

(Visited 23 times, 1 visits today)
Md Shamse Tabrej

Md Shamse Tabrej

Hi, I'm Shamse Tabrej. I'm an admirer of Excel who enjoys sharing and solving problems in Excel. I make an effort to investigate all of the choices so that I may point readers toward the most viable solutions. Both the globe and its problems are boundless. Let's assist one another in developing. Reader questions and suggestions are most welcome. 

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo