Left and Right Function

  • Thread starter Thread starter JG
  • Start date Start date
J

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
 
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
 
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
 
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 said:
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.
Click to expand...
....
 
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.
Click to expand...

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)
 
Back
Top