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 <seanrya...@yahoo.co.uk> 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 <seanrya...@yahoo.co.uk> 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 tab>view code>insert 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
<(E-Mail Removed)> wrote:

>> In Col C & D capitalise the first letter that the user inputs
>>
>> Thanks
>Right click the sheet tab>view code>insert 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