Change first letter of every word to Capital

[klmiura]

Does anyone have a Macro that I can use to change the first letter of every
word in an excel cell to Capital?

For example I have cell A1 with the contents of: private hospital

I would like to run the Macro and the cell to say: Private Hospital

Thank you,
Karen

 

[Jim Thomlinson]

There is a built in function for that.

=Proper(A1)

 

[Kevin B]

Insert a helper column to the right of column A and enter the following
formula:

=PROPER(A1)

Copy down to the last row and then select all of column B and copy it with
Ctrl+C.

Click on cell A1 and then right click on Cell A1 and select PASTE SPECIAL,
click the VALUES check box and click OK to replace all the original values
with there proper name format values. Delete column B when done.

 

[klmiura]

I have tried this and it does work...sort of; the problem is that I also have
names and it is not displaying them properly..ie McCarthy is being turned
into Mccarthy

 

[klmiura]

Also I have come things that are all suppose to be capital and they are
returning them with the capitalization removed...ie IBM is now Ibm

 

[Kevin B]

Assuming that the PROPER function has accomplished most of what you want, you
could use FIND AND REPLACE to fix your exceptions like "Ibm" and "Mccarthy".

 

[Chip Pearson]

Try a user defined function like the following:

Function Proper2(S As String) As String
Dim N As Long
Dim T As String
T = S
T = UCase(Left(T, 1)) + Mid(T, 2)
N = 0
Do
N = InStr(N + 1, T, Chr(32), vbBinaryCompare)
If N > 0 Then
T = Left(T, N) & UCase(Mid(T, N + 1, 1)) & Mid(T, N + 2)
End If
Loop Until N = 0
Proper2 = T
End Function

It will convert S to proper case, capitalizing only the first letter
of S and any character that is preceeded by a space. Thus,

tim mcGraw
will become
Tim McGraw
rather than
Tim Mcgraw


You can call this from a worksheet cell with
=Proper2(A1)


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Kevin B]

Thanks for the cool UDF, I'll be keeping that one handy...