create a text mask

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
>

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
>