How could I achieve the following when a user inputs data:-

In Col B capitalise the 2 digits after the first - (i.e. dash), note I
will have a second - (i.e dash) in the same cell but want to ignore
the 2nd one

In Col C & D capitalise the first letter that the user inputs

Thanks

Could you post some examples of the data that you expect to be input?

You can use PROPER to give you initial capital letters, like this:

=PROPER(C1)

but if you have more than one word in columns C and D then each inital
letter of each word will be capitalised. If you don't want this then a
formula like this can be used:

=UPPER(LEFT(C1)) & LOWER(RIGHT(C1,LEN(C1)-1))

Hope this helps.

Pete

On Dec 5, 8:39*am, Seanie wrote:
How could I achieve the following when a user inputs data:-

In Col B capitalise the 2 digits after the first - (i.e. dash), note I
will have a second - (i.e dash) in the same cell but want to ignore
the 2nd one

In Col C & D capitalise the first letter that the user inputs

Thanks

On Dec 5, 2:39*am, Seanie wrote:
How could I achieve the following when a user inputs data:-

In Col B capitalise the 2 digits after the first - (i.e. dash), note I
will have a second - (i.e dash) in the same cell but want to ignore
the 2nd one

In Col C & D capitalise the first letter that the user inputs

Thanks
Right click the sheet tabview codeinsert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 2 Then
Application.EnableEvents = False
fd = InStr(Target, "-")
ms = Left(Target, fd) & UCase(Mid(Target, fd + 1, 2)) _
& Mid(Target, fd + 3, 256)
Target = ms
Application.EnableEvents = True
End If
If Target.Column = 3 or target.column=4 Then
Application.EnableEvents = False
Target.Value = UCase(Left(Target, 2)) & _
Right(Target, Len(Target) - 2)
End If
Application.EnableEvents = True
End Sub

In Col C & D capitalise the first letter that the user inputs

Change both the 2's to 1 in this part of Don's code.

Target.Value = UCase(Left(Target, 2)) & _
Right(Target, Len(Target) - 2)


Gord Dibben MS Excel MVP

On Sun, 5 Dec 2010 06:12:59 -0800 (PST), Don Guillett Excel MVP
wrote:

In Col C & D capitalise the first letter that the user inputs

Thanks
Right click the sheet tabview codeinsert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 2 Then
Application.EnableEvents = False
fd = InStr(Target, "-")
ms = Left(Target, fd) & UCase(Mid(Target, fd + 1, 2)) _
& Mid(Target, fd + 3, 256)
Target = ms
Application.EnableEvents = True
End If
If Target.Column = 3 or target.column=4 Then
Application.EnableEvents = False
Target.Value = UCase(Left(Target, 2)) & _
Right(Target, Len(Target) - 2)
End If
Application.EnableEvents = True
End Sub