Issue with "Zero" format cell - Need a option to format

[harinsh]

Hi Team,

I'm facing lot of issues with my "zero" value cells.... I have one protected master data sheet where will just copy and paste the values. I will work on another file where I will do lot of formatting and functions. The problem is whenever I copy paste the zero values for example '000000 it won't accept it because of start character '.

I used text or custom format option and its not helping because I can see Zeros 000000 in cell but same wont be visible in formula bar, it's still shows only one zero '0'. Note that if I do convert number my zeros will go off.

I want to see my zeros (ex. 000000) in formula bar without character ' and also it should be number format. Can anyone help me getting this format.

Thanks,

 

[ChrisR]

Hi,

Why do you need 000000 to display in the formula bar?

 

[harinsh]

ok, my protected sheet accepts in that format only like 000000 should be in number format without character begins with '.

 

[ChrisR]

So you need it in that format for data validation purposes?

My question is, why do you need 000000 at all, especially in a number format?

From my understanding it will never appear as 000000 in the formula bar, as 000000 is not a number! Where as 0 is.

If you absolutely have to have the format of 000000 why can't you change the validation to accept the text format of this?

I might be missing something, but I can't see any reason for needing this in number format, as it can't be used in calculations anyway (As it's zero)

 

[harinsh]

Thanks for your suggestion.

I might not given you the proper explanation so, Let me give you one example here.....

Example:
I will receive data which contains like below
A1
'0050010
6559740
'0000014
'0656487

So, I want all these in numbers the moment I convert the number it will be as below....
50010
6559740
14
656487

Output should be;
0050010
6559740
0000014
0656487

Even if I do text format by selecting entire column but that character ' will still remains. This is not number format and it will be in text format. My protected sheet won't accept the text format. Hope I explained correctly.

Thanks again.....

 

[pgc01]

Hi

The formula bar displays the value. If you have a number value of 500 that's what you'll see in the formula bar. That's how we, users, can know what's the value in the cell.

If you want to see a specific format you'll have to see it elsewhere, in a cell or in some control.

 

[harinsh]

How do I get my required output then? Plz help me to get those numbers in text format and data should be in the below format only....

0050010
6559740
0000014
0656487