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