Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
There is a built in function for that.
=Proper(A1) -- HTH... Jim Thomlinson "klmiura" wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. -- Kevin Backmann "klmiura" wrote: 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "Kevin B" wrote: 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. -- Kevin Backmann "klmiura" wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "klmiura" wrote: 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 "Kevin B" wrote: 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. -- Kevin Backmann "klmiura" wrote: 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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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". -- Kevin Backmann "klmiura" wrote: 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 "klmiura" wrote: 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 "Kevin B" wrote: 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. -- Kevin Backmann "klmiura" wrote: 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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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) On Thu, 29 Jan 2009 07:59:01 -0800, klmiura wrote: 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 |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for the cool UDF, I'll be keeping that one handy...
-- Kevin Backmann "Chip Pearson" wrote: 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) On Thu, 29 Jan 2009 07:59:01 -0800, klmiura wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
excel changes the word true to capital letter | Setting up and Configuration of Excel | |||
change first letter of a word for an entire column | Excel Discussion (Misc queries) | |||
How do I format minuscule word in capital letter one in excell? | Excel Discussion (Misc queries) | |||
New Validation option to format 1st letter as Capital letter | Excel Discussion (Misc queries) | |||
Default Capital letter for 1st letter of a word | Excel Discussion (Misc queries) |