FORMATTING A 19 DIGIT NUMBER

[Wayne Richard]

I need some help to format a 19 digit budget code number. The format must be in this format: "XXXX-XXXX-XX-XXX-XXXXXX" (without the quotes).

An example of a budget code number that we use is: 1110-2110-38-068-611521

Thank you.

Wayne

 

[Bernard Liengme]

You can type the example as is into an Excel cell.
Do you mean you want to type just the digits and have Excel format it? Cannot be done directly; Excel has 15 digit limit.
1) Format the cells to hold the 'number' as Text
2) Enter value
3) In cell next to value use =LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,2)&"-"&MID(A1,11,3)&"-"&RIGHT(A1,5)
4) When all numbers are in, Copy the formatted value, use Edit|PasteSpecial->Values. Now you can delete the column with the original values

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address
I need some help to format a 19 digit budget code number. The format must be in this format: "XXXX-XXXX-XX-XXX-XXXXXX" (without the quotes).

An example of a budget code number that we use is: 1110-2110-38-068-611521

Thank you.

Wayne

 

[Opinicus]

Do you mean you want to type just the digits and have Excel format it?
Cannot be done directly; Excel has 15 digit limit.

Can it be done indirectly then? Is there a way to increase the number of
significant digits beyond 15? I want to use Excel to investigate the
properties of some *large* numbers.

 

[Bernie Deitrick]

Bob,

To use Excel to investigate the properties of *large* numbers, you either
need to use VBA only, which can go up to, IIRC, 30 digits, or use string
manipulations, in which case your numbers can go up to 32000 digits - but
will be slow.

HTH,
Bernie
MS Excel MVP

 

[Opinicus]

To use Excel to investigate the properties of *large* numbers, you either
need to use VBA only, which can go up to, IIRC, 30 digits, or use string
manipulations, in which case your numbers can go up to 32000 digits - but
will be slow.

In 1980 or thereabouts I could use a function in Apple Integer Basic to
manipulate integers up to the size of available memory. (Which IIRC was
32767 digits.) I was wondering/hoping Excel might have made some progress on
that.

 

[Wayne Richard]

O.K., so excel can only format up to 15 digits. What format would I use if I wanted to format a 13 digit number in the following format: XXXX-XXXX-XX-XXX ? I want to enter the data in a cell and have excel automatically format the number. (This number is a budget code.)

Any help would be greatly appreciated.

Wayne Richard


I need some help to format a 19 digit budget code number. The format must be in this format: "XXXX-XXXX-XX-XXX-XXXXXX" (without the quotes).

An example of a budget code number that we use is: 1110-2110-38-068-611521

Thank you.

Wayne

 

[Frank Kabel]

Hi
you may try the custom format:
0000-0000-00-000

 

[Niek Otten]

Hi Bob,

That is not what I recall.
I think the maximum number one could store in an integer was 32767. Which is
not the same as a number with 32767 digits, let alone decimal digits.
But do tell me if I'm wrong!
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Beege]

If you don't want to do any calculations, why not format cell as text?

I need some help to format a 19 digit budget code number. The format must be
in this format: "XXXX-XXXX-XX-XXX-XXXXXX" (without the quotes).

An example of a budget code number that we use is: 1110-2110-38-068-611521

Thank you.

Wayne

 

[Dave Peterson]

If it's to save typing the dashes (and to make sure it's formatted correctly),
you could format that range of cells (whole column???) as text and use a
worksheet event:

I used column A as my range to enter the data (adjust it as required).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant
Dim myStr As String

On Error GoTo errhandler:

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myStr = Right(String(19, "0") & Target.Value, 19)

myTempVal = CDec(myStr)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000\-0000\-00\-000\-000000")

errhandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior and select view
code. Paste this into the code window (usually on the right).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ps. You could also start your entry with an apostrophe '1234123412341234123

 

[Opinicus]

[Talking about Apple ][ Integer Basic]

I think the maximum number one could store in an integer was 32767. Which is
not the same as a number with 32767 digits, let alone decimal digits.
But do tell me if I'm wrong!

Integer Basic itself could only handle numbers in that range. But a compiler
for Integer Basic was released (by SC Software) that could handle numbers
and strings of any length.