I would like to create a text mask to format really long account numbers like
010-12345-0-22200-11122-55555
the user types digits and they are treated as text and displayed with the
dashes in the appropriate places

Use Format Cells/Number/Custom
Type the number format: 000-00000-0-00000-00000-00000
or ###-#####-#-#####-#####-#####.
The one with the zeros will hold the leading 000's on the front end.
--
Thanks, Kevin


"bryanp354" wrote:

I would like to create a text mask to format really long account numbers like
010-12345-0-22200-11122-55555
the user types digits and they are treated as text and displayed with the
dashes in the appropriate places

Excel only keeps track of 15 significant digits. Everything after the 15th
digit will become a 0.

You can preformat the cell as text or start with a leading apostrophe to get all
the numbers to show.

But then you'll have to format it manually.

Actually, you could have a worksheet event looking to see if that cell needs to
be reformatted.

But you'll still have to enter the numbers as text (preformat the cell as Text
or include a leading apostrophe: '1234123412341234123412

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

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

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "000-00000-0-00000-00000-00000")

errhandler:
Application.EnableEvents = True

End Sub

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

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

bryanp354 wrote:

I would like to create a text mask to format really long account numbers like
010-12345-0-22200-11122-55555
the user types digits and they are treated as text and displayed with the
dashes in the appropriate places

--

Dave Peterson

Dave, thanks a bunch... that's the best solution I've found yet!

"Dave Peterson" wrote:

Excel only keeps track of 15 significant digits. Everything after the 15th
digit will become a 0.

You can preformat the cell as text or start with a leading apostrophe to get all
the numbers to show.

But then you'll have to format it manually.

Actually, you could have a worksheet event looking to see if that cell needs to
be reformatted.

But you'll still have to enter the numbers as text (preformat the cell as Text
or include a leading apostrophe: '1234123412341234123412

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

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

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "000-00000-0-00000-00000-00000")

errhandler:
Application.EnableEvents = True

End Sub

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

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

bryanp354 wrote:

I would like to create a text mask to format really long account numbers like
010-12345-0-22200-11122-55555
the user types digits and they are treated as text and displayed with the
dashes in the appropriate places

--

Dave Peterson