Apostrophes in numbers with 16 characters

[Guest]

1. How I get rid of ' in the beggining of a number (data comes from Access I
believe) - it gets confused with text formats
2. Once I try to convert this to number, I lose the last character,
renumbered to "0"
3. If I try to multiply by 1 or sum 0 to make it a number, also loses the
last character, renumbered to 0.
4. If I try to combine this "renumbered" to 0 number, using RIGHT/MID with
the last character removed, it turns back to text once its combined (using &)

Thanks

 

[Guest]

You are facing one of the well-known Excel limits: Number precision of 15
digits.

For more information search Excel help for:
Excel specifications and limits

Check the "Calculations Specifications" section

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bernard Liengme]

Excel displays numbers with up to 15 digits - IEEE convention
The apostrophe is there to have Excel display text.
As you have seen, removing it loses the last (16th) digit.
Do you really need to do arithmetic on a number with this much precision?
If so, download the Xnumbers add-in (Goggle will find it)
best wishes

 

[David Biddulph]

1. How I get rid of ' in the beggining of a number (data comes from Access
I
believe) - it gets confused with text formats
2. Once I try to convert this to number, I lose the last character,
renumbered to "0"
3. If I try to multiply by 1 or sum 0 to make it a number, also loses the
last character, renumbered to 0.
4. If I try to combine this "renumbered" to 0 number, using RIGHT/MID with
the last character removed, it turns back to text once its combined (using
&)

Excel works to 15 significant figures. If you want to see 16 you'll need to
keep it as text.
You can, as you have realised, use text manipulation functions (such as
LEFT, MID, RIGHT) to pull out parts of the string and deal with them
separately (converting them to numbers if you wish).