What function does indentify the number of digits?

R

Ruslan

Hi All,
I want to create a formula that will identify the number of digits of a call
and if there are 4 digits it should add "0" at the beginning.
Please help me with that
 
B

Bob Phillips

=LEFT("0000",4-LEN(A2))&A2

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Ruslan

Dear Bob,
it did not solve the problem.
let me explain in another way:
let's suppose we have 5678 in cell A1, then I have to make B2 = 05678
but if A1 contains 5 digits like 95678, then B1 should be like A1

thank you in advance
 
B

Bernie Deitrick

I think Bob just missed that you wanted 5 digits.

=LEFT("0000",MAX(0,5-LEN(A1)))&A1

will also prevent errors if A1 is longer than 5 digits.

HTH,
Bernie
MS Excel MVP
 
S

silvest

I'm not exactly sure, but I think it's got something to do with th
'count'...

If count = 4, then =
LEFT("0000",4-LEN(A2))&A2

If count = 3, then
LEFT("000",3-LEN(A2))&A2

etc..
 
R

Ron Rosenfeld

Hi All,
I want to create a formula that will identify the number of digits of a call
and if there are 4 digits it should add "0" at the beginning.
Please help me with that
Click to expand...

Either custom format the cell: 00000

or use the formula:

=TEXT(A1,"00000")

Actually, the above is not quite what you asked. It will ensure that every
number has at least five digits, so a four digit number would have one leading
zero; a three digit number would have two leading zero's, etc.

If you ONLY want a number with four digits to have a leading zero, then use
this custom format:

[<999]General;00000

or this formula:

=TEXT(A1,"[<999]General;00000")

Again, the above only works for positive numbers. Do you need this to work for
negative numbers also?


--ron
 
Top