Exceeding the 15 digit max number precision

  • Thread starter Thread starter Joe Smith
  • Start date Start date
J

Joe Smith

Is there a way to exceed the 15 digit max? I'd like to be
able to apply a custom number format that would format a
17 digit number to appear as 000-000-000-000-000-00.

Thanks in advance.
 
Joe,

You're going to have to use strings to have 17 digits displayed, which
means that formatting won't work, which means you're going to have to
type in the dashes yourself, or use a string manipulation formula.

HTH,
Bernie
 
Thanks for your help Bernie.

I'm not familar with string manipulation formulas. Any
sites you can recommend I visit or keywords I should use
to learn more about this?

Much appreciated.
Joe
 
This should give you what you're looking for.

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

You can look up text functions in Help.
Depending on your version of XL, you may need to *first* open one of them
before you can display the entire list.

Look up "Text WorkSheet Function", and *then* click on "See Also", in order
to view the entire list, with links to their explanations.

Another good place is Peter Noneley's Function Dictionary add-in, which
describes them all.

http://homepage.ntlworld.com/noneley
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
You could take advantage of the first 15 digits
=Text(--left(A1,15),"000-000-000-000-000-") & Right(A1,2)
 
That's a darned good idea !
Did you just think of that ?
Never saw this here before (which isn't saying much, since there's a heck of
a lot I've missed).
--


Regards,

RD
 
If one wanted to use a Custom Function, here's one idea. This assumes all
17 characters are digits.

Function SpecialFormat(s As String) As String
'Dana DeLouis
On Error Resume Next
SpecialFormat = Format(CDec(s), "000\-000\-000\-000\-000\-00")
End Function
 
Back
Top