Hi,
I have names in one column, I need formula to convert the names in to proper
case. but if there is any 3 character word it should be in Upper case.
Plz Help...

eg.
AAA SYSTEM change into AAA system
DELL COMPUTERS change into DELL Computers
MOBILE BPL change into Mobile BPL
SIMMONS LAD change into Simmons LAD
A V SYS MEDIA PVT LTD change into A V SYS Media PVT LTD

It should change the case to upper for 3 letter words and rest to Proper case

Select a cell and then run this macro:

Sub Deepak()
Dim r As Range
Dim s As String, sOut As String

Set r = Selection
r.Value = WorksheetFunction.Proper(r.Value)
s = r.Value
ss = Split(s, " ")

For i = LBound(ss) To UBound(ss)
If Len(ss(i)) = 3 Then
ss(i) = UCase(ss(i))
End If
Next

sOut = ss(0)

For i = LBound(ss) + 1 To UBound(ss)
sOut = sOut & " " & ss(i)
Next

r.Value = sOut
End Sub

--
Gary''s Student


"Deepak" wrote:

Hi,
I have names in one column, I need formula to convert the names in to proper
case. but if there is any 3 character word it should be in Upper case.
Plz Help...

eg.
AAA SYSTEM change into AAA system
DELL COMPUTERS change into DELL Computers
MOBILE BPL change into Mobile BPL
SIMMONS LAD change into Simmons LAD
A V SYS MEDIA PVT LTD change into A V SYS Media PVT LTD

It should change the case to upper for 3 letter words and rest to Proper case