Text Formats

[Brad E.]

My Understanding: When using VALUES, one can format a cell to display a SSN
or ZIP+4 using Format Cells > Custom. Of course, Excel has a couple built
in, including these two, but you could make any format you want as long as
the entry is a value.

My Problem: I have part numbers which start with an Alpha character
followed by 7 digits, then a dash (minus) and two more digits. Is there a
way to format my cell entry to include the dash when the user just enters the
10-character part number?

Specific Example: If my part number if P1234567-89, users sometimes enter
P123456789. I would like to custom format the cell to automatically put a
dash in if it is not included. Not all my parts start with P.

 

[Gary''s Student]

Better still, the macro can auto-correct on the fly. Say data is being
entered into column A. Put the following event macro in the worksheet code
area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A = Range("A:A")
If Intersect(t, A) Is Nothing Then Exit Sub
v = t.Value
If Len(v) = 11 Then Exit Sub
If Len(v) = 10 Then
Application.EnableEvents = False
t.Value = Left(v, 8) & "-" & Right(v, 2)
Application.EnableEvents = True
End If
End Sub

So if the length of the entry is 11, it is left alone. If the length is 10,
then the dash is inserted.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
Const WS_RANGE As String = "B1:B10" 'adjust range to suit
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells
With Cell
.Value = Left(Cell, Len(Cell) - 2) & "-" & Right(Cell, 2)
End With
Next
End If
On Error GoTo 0
Application.EnableEvents = True
End Sub

No error-checking for entries less than or greater than 10 characters.


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

As others have pointed out, you can use event programming to correct the
entry (formatting would only change the display of the entry, not the actual
entry itself, so formatting would not be the appropriate way to do what you
asked). Here is my Change event suggested solution...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C")) Is Nothing Or Target.Value = "" _
Or Target.Value Like "[a-zA-Z]#######-##" Then Exit Sub
If Target.Value Like "[a-zA-Z]#########" Then
Application.EnableEvents = False
Target.Value = Left(Target.Value, 8) & "-" & Right(Target.Value, 2)
Application.EnableEvents = True
Else
MsgBox "That entry is incorrect", vbCritical, "Bad Entry"
End If
End Sub

You can set the column to monitor in the first If statement (inside the
Columns property call)... just change it to the column letter you want to
apply this functionality to. Also note that the code will warn the user if
the entry does not meet the correct format (letter followed by 9 digits).

 

[GS]

Adding to the posted suggestions...

Rather than using Left(), Right() and concatenation:

Target.Value = Format(Target.Value, "!@@@@@@@@-@@")

Garry