PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

(adsbygoogle = window.adsbygoogle || []).push({});

Contents

ADD LEADING ZERO IN EXCEL

INTRODUCTION

EXCEL provides us with a vast variety of options to do almost whatever we wish to do concerned with our data analysis. And if, by chance, something is not available, we have the tools with the help of which , we can do that easily.
Similarly a very simple but challenging situation occurs when we need some leading zeroes before a number in EXCEL.
In this article we’ll discuss how to sort out this problem easy and fast.
We’ll discuss different ways to apply leading zero to Text as well as Number.

 

(adsbygoogle = window.adsbygoogle || []).push({});

ADD LEADING ZERO TO TEXT IN EXCEL

There is nothing to be done if we need to add zero to the text.

Simply, add the zero anywhere in the field and it would sustain. If somehow there is any problem, follow the following steps.

  • Select the cell containing text.
  • Right Click the text and choose FORMAT CELLS.
  • Choose TEXT format for the cell.
  • We can also choose TEXT FORMAT from the HOME TAB directly.

ADD LEADING ZERO TO ANY NUMBER IN EXCEL

Adding leading zero to a number is the tricky one for many of us.

Suppose we have enter the bank account number. some bank account numbers have leading zero

or

We want to create a series and want to have that all the number have same number of significant digits.

In both of the cases we are in trouble.

WHERE IS THE PROBLEM??

The Excel removes all the leading zero from the numbers which we type in Excel. So it becomes a problem for us.

SOLUTION

We have many solutions for this problem. We’ll discuss the main two of them. But the solution will depend on the

nature of the requirement.

SOLUTION 1:IF THE NUMBER NEED NOT TO DO ANY MATHEMATICAL TASK

If the number, which needs the leading zero, need not to be involved in any type of mathematical task, which means

that we are not going to apply any formula on that number, we can simply change the format of the cell to TEXT.

STEPS:

Select the cell containing text.

(adsbygoogle = window.adsbygoogle || []).push({});
LEADING ZERO TO NUMBER IN EXCEL
EXCEL: APPLY LEADING ZERO TO A NUMBER

SOLUTION 2:IF THE NUMBER NEEDS TO BEHAVE LIKE A NUMBER ONLY

A situation may arise when we need the leading zero and still number needs to be a part of the function or formula, it needs to behave like a number only.
The SOLUTION 1 won’t work for this as if we convert the format to TEXT, it won’t be able to participate in a function or formula.
Here we’ll make use of the custom format.
CUSTOM FORMAT IS THE FINAL DESTINATION FOR ALL FORMAT RELATED PROBLEMS.
STEPS
  • Right click the cell (which needs the format change) and click format cells.
  • The format dialog box will open.
  • Go to the bottom and choose CUSTOM.
  • Click on GENERAL and delete it and enter 000… and look on the top of the dialog box, where sample is shown.
  • Stop when sample shows the format you want.
  • Click ok
apply leading zero to a number in excel
EXCEL:APPLY LEADING ZERO TO A NUMBER IN EXCEL
(adsbygoogle = window.adsbygoogle || []).push({});

finding last USED ROW in a SHEET

Let us find out the last used row in a column i.e. the row which contains the last data in a row. We can take the leads from the above examples of finding the last used cell in a column.

SOLUTION 1-find last USED ROW IN A SHEET

Let us try to check the cells of a column and find out the last filled cell in a column. Usually this kind of task is easily done in VBA but we will still try in Excel. We’ll take help of LOOKUP function in this as this is a very basic function for looking up a value in a given table and it runs throughout the range.

Suppose We have the following data in a column.

RANGE
1
 
3
23
4
54
5
 
 
 
 
6

 

FIND LAST FILLED CELL IN A COLUMN
EXCEL:FIND LAST USED CELL IN A COLUMN

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Share this:

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: