Change first letter of every word to Capital

K

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
 
K

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.
 
K

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
 
K

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
 
K

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".
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top