- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Convert Zeros to Blank in a Selected Range in Excel?
In Excel, you may need to convert zeros to blank cells at times. If we attempt to complete the task manually, it can be a time-consuming process. Read this tutorial to learn how you can convert zeros to blanks in a selected range in Excel. The blank represents the value of being empty and may or may not be filled in the future. Sometimes zeros can also be considered a value. We can complete the process using the Find and Replace and VBA applications.
Converting Zeros to Blank in Excel
Here we will first open the find and replace function, then replace the zeros with blanks. Let's look at a simple procedure for converting zero to blank in Excel using find and replace. We can use this process if we need to apply it to the whole worksheet.
Step 1
Let us consider an excel sheet where the data is a list of numbers including some zeros, similar to the below image.
Now, press CTRL + F to open find and replace.
Step 2
Now in the pop-up, click on Replace, then in Find, enter 0, and in Replace, enter Space.
Replace > 0 > > Replace all
If we need to apply the process to a selected cell, we can use the help of a VBA application.
Converting Zero to Blank in a Selected Range
Here we will first open the VBA application, then enter and copy the code into the textbox. Let's look at a simple procedure for converting zero to blank in an Excel range.
Step 1
Let us consider the same data that we used in the above example.
Now right-click on the sheet name and select view code to open the vba application, then click on insert and select module.
Then type the below-mentioned programme in the text box as shown in the below image.
Right click > View code > Insert > Module > programme
Program
Sub ChangeZero() 'Updated By Nirmal Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Convert Blank cells" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng If Rng.Value = 0 Then Rng.Value = "" End If Next End Sub
Step 2
Then save the sheet as a macro-enabled workbook, click F5 to run the code, select the range of cells you want to change to blank, and click OK, as shown in the below image.
Save > F5 > Select range > OK
Conclusion
In this tutorial, we used a simple example to demonstrate how we can convert zeros to blanks in Excel.