How to create a formula/format for a long number to include a period and not round

We have a large spreadsheet of that includes lists of numbers 18 digits in length, that are sent to us from an outside source. They begin with an apostrophe. When I try to create a custom number format, then remove the apostrophe, the number automatically adds a zero at the end and won't keep the number. Example: 530040297030000006 should look like 53-00-40-297-029.000-006. I added this custom format: ##-##-##-###-###\.###-### but it removes the 6 from the end of number and replaces it with a zero. I don't know if it's rounding, and how to stop that. I'm pretty new at this, and can't figure out what I need to do to keep that from happening. Any help would be much appreciated. Thanks.

Excel has a limit of 15-digit precision for numbers - anything larger than that has to be treaded as a text value if you want to preserve all the digits, and of course a custom format is no use for text. The apostrophe forces those digits to be treated as text. If you want to transform that string of digits to have hyphens between some of the digits, then you will need a formula in an adjacent column, along the lines of:

=LEFT(A1,2) &"-"& MID(A1,3,2) &"-"& MID(A1,5,2) &"-"& MID(A1,7,3) &"-"& MID(A1,10,3) &"."& MID(A1,13,3) &"-"& RIGHT(A1,3)

You can copy this down and use copy | paste special | values to convert the formulae to values, and then you can copy/paste over the original values if you need to and then delete the helper column.

Hope this helps.

Pete

Oh wow, this is almost working. The 1st formula from Pete gives the result of: '5-30-04-029-703.100-006
and the second one: '53004029703000-006. Am I doing something wrong?

Ah, I think the cell must be formatted as Text, so the leading apostrophe is taken as being part of the value. Try this amendment to my formula:

=MID(A1,2,2) &"-"& MID(A1,4,2) &"-"& MID(A1,6,2) &"-"& MID(A1,8,3) &"-"& MID(A1,11,3) &"."& MID(A1,14,3) &"-"& RIGHT(A1,3)

Hope this helps.

Pete

That absolutely worked! Thank you so much. Our assessor's department is going to be thrilled with this formula since they have huge amounts of data in this spreadsheet that needs converted. I would have never been able to figure this out. A million thanks!
Rita

You're welcome, Rita - glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Done. Thank you for the guidance. I just joined this forum today.

Well, thanks for that, and come back anytime you have a problem with Excel - there will always be someone here to help.

Have a good weekend.

Pete