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

create a text mask
 

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

create a text mask
 

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

create a text mask
 

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