Code or formula for capitalizing, excluding acronyms

[Kat Morgan]

Hi all,

I am looking for vba code or a macro the will capitalize the first letter of each word, and does not make any changes to acronyms.

I am aware of the proper function, however it changes the letters in an acronym to lower case, except for the first. Is there a way to do this, either by maybe having the code exclude words in the cell that are 3 characters or less, or by excluding words that already start with a capital? I would love any help or suggestions!

Here are examples of what I am looking for:
From: hi my name is kat
To: Hi My Name Is Kat
From: managers IMR Report june
To: Managers IMR Report June

Thanks!
Kat

 

[pgc01]

Hi Kat
Welcome to the board

1 -
You have to give a rigorous definition of what will be considered an acronym and what to do with the other words.

Ex.

All the words will have the first letter capitalized except words that are all capitals. These last words are not changed.

Using a definition like you posted (excluding words that already start with a capital), does not seem to work. For ex., in: "Hi, my name is Kat", there are no acronyms, there are words that start with a capital just because of their position or their nature.

2 - you have to define how words are separated. In your examples they are always separated by spaces. This could be part of the definition for the code.



If this is acceptable, you could use a vba udf to change each word.

 

[Kat Morgan]

Hi PGC,

Thanks for your reply. I'm not sure I understand what you are saying, or perhaps I chose a poor example to give. The clearest way I can think of to explain what I am looking for is either a formula or code that will capitalize the first letter of each word in a cell, and not effect any of the other letters. It does not matter what the words are, they could just be gibberish, as long as the first letter is capitalized.

I was able to get help from another forum in terms of coding, however it only works when you make changes to a cell. This basically defeats the purpose as if 1000 cells are being used, I would have to go in to each cell individually for the code to make the capitalizations. It would be fantastic if I could have a macro that I could link to a button, that would automatically run on all of the cells.

Here is the code that I was given:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Or Target.HasFormula Then Exit Sub
Dim vWORDS As Variant, i As Integer
vWORDS = VBA.Split(Target, " ")
For i = LBound(vWORDS) To UBound(vWORDS)
If vWORDS(i) <> UCase(vWORDS(i)) Then vWORDS(i) = VBA.UCase(VBA.Left(vWORDS(i), 1)) & VBA.LCase(VBA.Mid(vWORDS(i), 2, 1000))
Next
Application.EnableEvents = False
Target = VBA.Join(vWORDS, " ")
Application.EnableEvents = True
End Sub

Any suggestions are greatly appreciated.

Thanks,
Kat

 

[pgc01]

Hi

I was thinking of using a udf to convert the strings.

For ex., in B2:

=Capit(A2)

Copy down

Function Capit(s As String)
Dim v As Variant, j As Long

v = Split(s, " ") ' separates the words
For j = LBound(v) To UBound(v)
    If StrComp(v(j), UCase(v(j)), vbBinaryCompare) <> 0 Then v(j) = StrConv(v(j), vbProperCase)
Next j
Capit = Join(v, " ") ' joins the words
End Function

Ex.:

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Hi, I am Kat</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Hi, I Am Kat</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Hi, I work at UNICEF</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Hi, I Work At UNICEF</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">INTEL and AMD produce microprocessors</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">INTEL And AMD Produce Microprocessors</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">The presidents of the USA and Argentina <br>will meet today</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">The Presidents Of The USA And Argentina <br>Will Meet Today</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet5</td></tr></table>

 

[Kat Morgan]

Hi PGC,

Thanks so much! Worked like a charm! I have been looking for a solution for this quite a long time, so your solution is fantastic. Can't thank you enough!

Cheers,
Kat

 

[pgc01]

You're welcome. Thanks for the feedback.