Exceeding the 15 digit max number precision

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.
 
B

Bernie Deitrick

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
 
J

Joe Smith

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
 
R

RagDyer

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!
==============================================
 
T

Tom Ogilvy

You could take advantage of the first 15 digits
=Text(--left(A1,15),"000-000-000-000-000-") & Right(A1,2)
 
R

RagDyer

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
 
D

Dana DeLouis

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
 
Top