Insert "/" in the middle of the string

  • Thread starter Thread starter celia
  • Start date Start date
C

celia

The purpose of this is to make sure the users have enter the correct
data in the correct format.

The correct format is like this: P/2003
So, whenever the users enter a "P" automatically a "/" is inserted to
the text. Or if the users just enter P2003 only, then P2003 is changed
to P/2003 automatically.

Is this possible to do in Excel?

Please advice.

Thanks.
 
Yes it is possible

how it is done will depend on how the data is beingt entered -
directly into the spreadsheet or via form/input box
 
It is directly entered into the spreadsheet without any inpu
box/forms.

How can it be done? Any functions? Please advice.


Any and all help would be appreciated.

Thanks
 
You could use a worksheet event that looks for entries in that format.

rightclick on the worksheet tab that should have this behavior and select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Target.HasFormula Then Exit Sub
If Intersect(Target, Range("b:b")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If UCase(.Value) Like "[A-Z]####" Then
Application.EnableEvents = False
.Value = UCase(Left(.Value, 1)) & "/" & Right(.Value, 4)
End If
End With

errHandler:
Application.EnableEvents = True

End 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
 
Back
Top