Using LEFT, can excel produce the first 0's in a custom format?
 

I have a custom format (00000) on cell A1. The data is 00251. I have a
formula in B2 to read the first 3 digits in A1 (Left, A1, 3). How do I get
B2 to pick up the data 002 instead of 251? Excel won't read the preceding
0's.

=LEFT(TEXT(A1,"00000"),3)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"kanye" wrote in message
...
I have a custom format (00000) on cell A1. The data is 00251. I have a
formula in B2 to read the first 3 digits in A1 (Left, A1, 3). How do I
get
B2 to pick up the data 002 instead of 251? Excel won't read the preceding
0's.

The leading zeros aren't *really* there so far as values are concerned. Only
the illusion of leading zeros when you use a custom format

Try

=LEFT(TEXT(A1,"000000"),3)



"kanye" wrote:

I have a custom format (00000) on cell A1. The data is 00251. I have a
formula in B2 to read the first 3 digits in A1 (Left, A1, 3). How do I get
B2 to pick up the data 002 instead of 251? Excel won't read the preceding
0's.

=TEXT(LEFT(A10,LEN(A10)-2),"000")

--

HTH

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


"kanye" wrote in message
...
I have a custom format (00000) on cell A1. The data is 00251. I have a
formula in B2 to read the first 3 digits in A1 (Left, A1, 3). How do I
get
B2 to pick up the data 002 instead of 251? Excel won't read the preceding
0's.

Thanks to you both.

"kanye" wrote:

I have a custom format (00000) on cell A1. The data is 00251. I have a
formula in B2 to read the first 3 digits in A1 (Left, A1, 3). How do I get
B2 to pick up the data 002 instead of 251? Excel won't read the preceding
0's.