Capitalise on Input Q
 

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

Capitalise on Input Q
 

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

Capitalise on Input Q
 

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

Capitalise on Input Q
 

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