I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

You could convert your numbers to text to allow the leading zero but why not
just modify your formula something like this...

=IF(LEN(A1)=5,LEFT(A1,3),LEFT(A1,2))
--
HTH...

Jim Thomlinson


"JG" wrote:

I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

Try this in place of your regular Left formula:

=LEFT(REPT("0",5-LEN(A1))&A1,3)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"JG" wrote in message
ps.com...
I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

You can format as text or perhaps use this formula instead

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

where A1 contains your data

"JG" wrote:

I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

Thanks all, so many
choices.............=IF(LEN(A1)=5,LEFT(A1,3),LEFT( A1,2)) .this is the
on I used. I can understand it except for the LEN. Could someone
explain what it does.

Thank you



daddylonglegs wrote:
You can format as text or perhaps use this formula instead

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

where A1 contains your data

"JG" wrote:

I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

LEN, in common with almost all Excel functions, has information within
Excel's Help.
Click on the function in the formula bar and press the fx button beside the
formula bar, or enter the function name into Excel's help window.
--
David Biddulph

"JG" wrote in message
oups.com...
Thanks all, so many
choices.............=IF(LEN(A1)=5,LEFT(A1,3),LEFT( A1,2)) .this is the
on I used. I can understand it except for the LEN. Could someone
explain what it does.
....

Len (short for length) returns the length of the cell it is pointed at. So If
the number is 5 digits then grab the left 3 digits otherwise grab the left 2
digits...
--
HTH...

Jim Thomlinson


"JG" wrote:

Thanks all, so many
choices.............=IF(LEN(A1)=5,LEFT(A1,3),LEFT( A1,2)) .this is the
on I used. I can understand it except for the LEN. Could someone
explain what it does.

Thank you



daddylonglegs wrote:
You can format as text or perhaps use this formula instead

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

where A1 contains your data

"JG" wrote:

I have a cell with a 5 digit number 12345. I use the left function to
separate the first 3 numbers (123) and the right function to separate
the right 2 numbers (45). 99.99% of the time the left three numbers
will be from 100 to 600. As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

Thanks for all your help.......Again
JG

JG wrote...
. . .As unlikely as it would be there could be a
time that it would look like 9901. I thought I could enter in the
number like 09901 but excel does not like this. The way I am doing it
now 9901 would be seen like 990 not the 99 that I need. Is there a way
to format a cell so Excel will add the 0 to the left so the left
function will work.

You could format the cell as Text before entering the strings of
numerals. That'd retain any leading zeros, but the cells would be
treated as text rather than numbers in formulas referring to those
cells (which seems to be appropriate in this case).

FTHOI, another alternative formula work-around using only LEFT and
RIGHT functions.

=LEFT(RIGHT("00000"&x,5),3)