Default Capital letter for 1st letter of a word

[Jeff]

Hi, I am trying to auto format excel entry to default capital letter for 1st
letter of each word. For example: When user insert '10, international
building, sunny road', the entry will updated to '10, International Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.

 

[Ardus Petus]

=PROPER(A1)

HTH

 

[Jeff]

Hi Ardus

Thanks for you reply. Your recommended method is to use formula =PROPER(A1).
It's referring to another cell, update to correct format and appear in
existing cell. Is there a way to update to correct format immediately in
existing cell withot referring to another field ? Some kind of excel
validation function.

Jeff.

 

[Ardus Petus]

AFAIK, this does not exist.
But you can copy/Paste special values from calculated column back into
original column.

Cheers,

 

[Jeff]

Ardus,

Thanks. I will suggest to Microsoft to include this as one of excel
validation option.

Jeff.

 

[Ardus Petus]

You could use a Worksheet_Change event procedure to "properize" entries in
certain cells.

 

[Jeff]

Ardus

I got reply from Excel Expert Bob Philips, the solution is as follow

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsNumeric(.Value) Then
If Not .HasFormula Then
.Value = Application.Proper(.Value)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

====================================================