custom number format

[niting]

hi forum,


I have sheet with employees id and bank account against it. The problem i am facing is that the bank account numbers can start with 0's. In that case i cant make excel show the acount nos. unless i make it appear as text. However, for cross checking purpose, it is needed to have dem as nos.


Any idea how to show the bank accnts, as dey are if dey dont start with 0's and show dem starting with 0's when dat is the case. I tried zip code format but dint work.


Also, if i receive a file with numbers hardcoded as text with ' used before the number, do i have to manually remove the ' sign for all the entries or a formula cld take care of it???


Thanks

Nitin g

 

[Hui]

Using a Custom format of 00000.00

Change decimals to suit

eg

00000

00000.0

00000.00


add extra leading 0's to suit

eg 0000000000.00 for ten numbers with leading 0's

 

[niting]

Hui,


afraid, it does not work. with the format when i start a number with lading 0, it still shows the number without the leading 0.


Thanks

 

[Hui]

If you use the custom format

000000 (6, 0's)

and enter 125 it will display as 000125

if you enter 0125 it will display as 000125


what do you want ?

 

[niting]

sorry hui,


did not read d last part of ur comment. it works. Thanks


using dis sheet i create a master sheet. However, when i recieve the sheet from our units, dey come hardcoded as text. and if i do simple, B1=C1 against an employee code, it shows false. How do i deal with that situation wherein, i can compare the numbers, one hardcoded as text and one with the custom number format like d one above.


Thanks

 

[Hui]

Use

=Value( Ref )

to convert it to a Value