Problem in Integer Value

Hi All,

I have some list of numbers which is generated by application while creating a Emp Id's and when i am trying to convert that value in Number/General format the last digit is replacing with zero.

exactly i don't know why this happening but as per my understand that digit exceeding the integer limit and help is more appreciate.

attached file will give u the clear picture.

Thanks,
Naveed.

why because while generating the id's in each numbers in starting (space & single cote) is coming and when i am removing spaces and single cote the last digit(means 1 is replacing with 0).

once you copy by pressing F2 and copy only number & paste in other cell and check it out u will come to know

its because the cell is showing as text
when you try to change to a number the most that can be used is 15 significant digits in a number, so with
http://support.microsoft.com/kb/269370

MS chose to limit display to 15
digits to avoid questions when unrepresentable 16 digit numbers, such as
2^53+1 = 9007199254740993
get changed to different 16 digit numbers (9007199254740992) that are
representable.

sir, any solution for the same is more appreciate

no solution, if you are turning the text into a number, thats a limit in excel -

otherwise you would need to split the text across two cells

still not sure what you are trying to do, as its text and why convert to a number ?

I notice that the last digit is a 1 and then a lot of zeros in between
perhaps you could just extract the part with the numbers and use those and then recombine to make a new ID

I tried this only sir and using the function like (trim,clean,right) and its working perfectly if u kept the formula in cell but my manager does not want formula in cell.

anyway thanks for this valuable clarification, this excel forum has very much talented masters. thank you once again !

in which case you will needto use VBA , if you cant have a formula in the cell

can you use VBA at all - is that not allowed at your company ...

I dont know enough about VBA- very rusty now and not competent to post on a forum to help others

I am not well good in VBA , i am just good in VBA and i tried with that also but still that last digit getting replace.

if u provide the code for the same then i appreciate sir.

As i said I cant do VBA here
and how have you split and cleaned the cells using formulas - thats what you would need to replicate in VBA - the 15 digits still apply in VBA