HELP IN A FORMULA

[Kashif]

I have a column with two digit & three digit values e.g. 15, 777 which i want
to convert in four didgit value in next column as 0015, 0777.
Can i use a formula in the column that adds no of ZEROs in the value
bringing it in a FOUR DIGIT VALUE?

I hope my question makes sense. Any help will be greatly appreciated.

 

[Teethless mama]

=TEXT(A1,"0000")

 

[T. Valko]

You don't necessarily need a formula to do this. You can format the numbers
to *display* as 0015 and 0777.

Select the range of cells in question.
Goto Format>Cells>Number tab
Select: Custom
Under Type enter 0000
OK out

Note that Excel does not recognize leading zeros in numbers. The true
underlying values will still be 15 and 777.

 

[admin]

You don't necessarily need a formula to do this. You can format the numbers
to *display* as 0015 and 0777.

Select the range of cells in question.
Goto Format>Cells>Number tab
Select: Custom
Under Type enter 0000
OK out

Note that Excel does not recognize leading zeros in numbers. The true
underlying values will still be 15 and 777.

If you need to save the file in text formats (.csv or .txt ....) you
need to ensure the you keep the leading zero's and MS Excel is .


try IF( LEN(A1) = 2, "00" & A1, IF ( LEN(A1) = 3, "0" & A1,))

if you need more than 4 or 5 arguments, put this into a lookup table

Regards

Dougal Everingham
Senior Solutions Consultant

CONTROL PLAN REPORT ANALYSE IMPROVE

Australia
Level 10, 390 St Kilda Road, Melbourne VIC 3004

(e-mail address removed)
www.satorigroup.com.au

The Satori Group is an international consulting company specialising
in designing and implementing business improvement solutions

 

[Kashif]

Thanks a lot.

=TEXT(A1,"0000")