Left and Right Function

[JG]

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

 

[RagDyeR]

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!
=====================================================

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]

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

 

[David Biddulph]

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

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.

....

 

[Harlan Grove]

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)