Solved: How can I change text to true proper case?

[calbais]

Hi
I would like to know how to use VBA to change names to their proper case. I know how to make it change the first letter but how do I get names like McDonald, O`Leary, Van Huesen, etc to be properly formatted? I've searched the Internet for a few days now and there doesn't seem to be much help there. In the past I found someone's code that listed the names in lower case separated by commas and then another line had the same name in the proper format and the program would search any selected text for the name and then change it to the proper format but I can't seem to find it again now!
Does anyone have any ideas on how to do this?
By the way, I'm not a very advanced VBA programmer but enjoy learning the language!
Thanks for any respones!

[rruckus]

I don't believe you can use the StrConv functions for this, but you can probably write a function to get to the same place using the spelling suggestions.

Check to see if the SpellingErrorType = wdSpellingCapitalization then fix it.

[fumei]

The problem is Word does NOT consider

McDonald

to be "proper" case. It simply does not care. Mcdonald...McDonald. Word has no value for the later as being correct. Nor more than McdoNald as being correct.

You will have to build your own string logic. There is no existing Word or VBA function that will do it for you. You will have parse and test every letter.

[rruckus]

fumei: as I said, Word WILL recognize mcdonald vs McDonald in the Spelling Capitalization suggestion which you can read the suggestions and replace the text using VBA.

[Frosty]

To the OP:

There are a couple of ways to address this. But they all kind of stink. rruckus is suggesting a way that would probably be more flexible, but also require a good bit of advanced coding to make it that flexible. Since you've already said you're not an advanced coder and rruckus doesn't seem willing to put give a demo, I would suggest the methodology you saw before. Something along the lines of...

Sub FixNamesInMySelection()
Dim rngSearch As Range
Dim i As Integer
Dim aryProperCase As Variant

'quick and dirty method for creating an array of your properly cased names
aryProperCase = Split("McDonald|O'Leary|Van Heusen", "|")

'get the search range
Set rngSearch = Selection.Range

'brute force-- you'll replace any matches with proper casing whether they like it or not
For i = 0 To UBound(aryProperCase)
With rngSearch.Find
.Text = aryProperCase(i)
.Replacement.Text = aryProperCase(i)
.Wrap = wdFindStop
.Execute Replace:=wdReplaceAll
End With
Next

End Sub

This is neither pretty nor elegant... but it solves the immediate need. Just keep adding to the string if you need more names. If you get much above 20 names, I'd suggest storing your data in another location and beginning to separate the functionality. At the moment, all it does is replace all examples of the text within whatever you have selected.

Hope this at least points you in the right direction.

[calbais]

Thanks for all the suggestions. I have found code that almost works as I want it without listing each name. It's a function I found in one of the forums. Here it is if anyone is interested in properly formatting names automatically.
This function goes in the module:
Function ProperCase(strOneLine As String, intChangeType As Integer) As String
'---------------------------------------------------------------
'- This function will convert a string to Proper Case -
'- The initial letter of each word is capitalised. -
'- It will also handle special names such as O', Mc and -
'- hyphenated names -
'- if intChangeType = 1, all text is converted to proper case. -
'- e.g. 'FRED' is converted to 'Fred' -
'- if intChangeType = 0, upper case text is not converted. -
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains unchanged. -
'---------------------------------------------------------------
Dim I As Integer
Dim bChangeFlag As Boolean
Dim strResult As String
'----------------------------------------------------------
'- No characters in string - nothing to do -
'----------------------------------------------------------
If Len(strOneLine) = 0 Then
ProperCase = ""
Exit Function
End If
'----------------------------------------------------------
'- Always set first letter to upper case -
'----------------------------------------------------------
strResult = UCase$(Left$(strOneLine, 1))
'----------------------------------------------------------
'- Now look at the rest of the string -
'----------------------------------------------------------
For I = 2 To Len(strOneLine)

'----------------------------------------------------------
'- If the previous letter triggered a capital, change -
'- this letter to upper case -
'----------------------------------------------------------
If bChangeFlag = True Then
strResult = strResult & UCase$(Mid$(strOneLine, I, 1))
bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case if required -
'----------------------------------------------------------
Else
If intChangeType = 1 Then
strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
Else
strResult = strResult & Mid$(strOneLine, I, 1)
End If
End If

'----------------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen found -
'----------------------------------------------------------
Select Case Mid$(strOneLine, I, 1)
Case " ", "'", "-"
bChangeFlag = True
Case Else
bChangeFlag = False
End Select
Next I
'----------------------------------------------------------
'- Special handling for Mc at start of a name -
'----------------------------------------------------------
If Left$(strResult, 2) = "Mc" Then
Mid$(strResult, 3, 1) = UCase$(Mid$(strResult, 3, 1))
End If

I = InStr(strResult, " Mc")
If I > 0 Then
Mid$(strResult, I + 3, 1) = UCase$(Mid$(strResult, I + 3, 1))
End If

'----------------------------------------------------------
'- Special handling for Mac at start of a name -
'----------------------------------------------------------
If Left$(strResult, 3) = "Mac" Then
Mid$(strResult, 4, 1) = UCase$(Mid$(strResult, 4, 1))
End If

I = InStr(strResult, " Mac")
If I > 0 Then
Mid$(strResult, I + 4, 1) = UCase$(Mid$(strResult, I + 4, 1))
End If

ProperCase = strResult
End Function

and this line goes in a macro which you call whenever you need to use the function. The text has to be selected before running the macro.

sub FixCase()
Selection.Text = ProperCase(Selection.Text, 0)
end sub

The only problem I have with this is that names like o'shea or o'leary aren't formatted correctly. It will capitalize the "o" but not he letter after the apostrophe... I can't figure out why? If anyone knows, please post the correction. I tried the same code using a text box and it will format the " o' " words ok but not if it is just a line of text that is selected outside of a textbox which is how I have to use it.

Thanks for any help!

[Talis]

If under Tools - AutoFormat As You Type you have checked "Straight quotes" with "smart quotes" then a straight apostrophe will become a right curly single quotation mark and will not be found by your code.

Change the line:
Case " ", "'", "-"
to include a right curly single quotation mark and it will work OK.

[calbais]

Thank you, Talis!! I have never heard of curly quotes or how to set/unset them in Word. I entered the curly quote using ALT + 0146 and now the code works properly. I want to thank everyone who helped to get this working. There doesn't seem to be a lot of code out there for setting text to proper case with words like o'whatever and mc whatever but this code does it very well.
It still screws up "mack" making him "MacK" (with a capital K) but at least now, the majority of names will be correct.
Thanks everyone!!

[macropod]

It still screws up "mack" making him "MacK" (with a capital K) but at least now, the majority of names will be correct.
You can fix that by replacing the exisiting 'Mac' code with:
If Left$(strResult, 3) = "Mac" Then
If Len(strResult) > 5 Then
Mid$(strResult, 4, 1) = UCase$(Mid$(strResult, 4, 1))
End If
End If
I = InStr(strResult, " Mac")
If I > 0 Then
If Len(strResult) > I + 5 Then
Mid$(strResult, I + 4, 1) = UCase$(Mid$(strResult, I + 4, 1))
End If
End If
PS: When posting macros, please use the VBA tags.

[calbais]

Thanks Macropod (Paul). I didn't know about VBA tags when I posted. I will use them in the future. Much neater!

Your code works find for the word "mack" alone but when I used "mack mackenzie" it also capitalized the "k" in "mack"!

[Talis]

Glad you've got it working. Well done!

The way I add particular characters is by using chr.

So the line:
Case " ", "'", "-"
would become:
Case " ", "'", "-", chr(146)

By the way, another problem with the code is it changes:

I'm I'd I've => I'M I'D I'Ve

This is another little tweak that's needed if such abbreviations are likely to be in the selection for capitalization.

[calbais]

Thanks Talis! I didn't know you could use chr(146), etc. Thanks for the tip. As for words like I'm, I've etc.; I am only dealing with people's names so it shouldn't be a problem.
I appreciate your help!!

[macropod]

Your code works find for the word "mack" alone but when I used "mack mackenzie" it also capitalized the "k" in "mack"!
You can fix that by changing:

If Len(strResult) > 5 Then
to:

If Len(Split(Trim(strResult), " ")(0)) > 5 Then
Ultimately, you're unlikely to be able to trap every possibility with these kinds of rules, or with a lookup array (so far the code only handles exceptions for 'Mc" & 'Mac'), so you're going to have to validate the output whatever you do.

[Frosty]

That's kind of the problem with the whole string manipulation approach-- there's always going to be something you didn't think of. For instance-- what about trapping for hard spaces (they don't look like " " to code, although they tend to look the same to the Find object). Or when you have a period, semi-colon... or if someone has typed "Mack-- now, that's a great guy."

If it were me, I would stick to simply adding names to the list as you encounter them (and think of them). There are a lot of ways to have that list be very friendly and easy to add to... much easier (in my mind) than trapping for partial words, and having to worry about whether you've corrected for every situation.

This is an excellent thread on the power (and limitations) of string manipulation. But I think you can get what you want easier by using the built-in AutoCorrect feature, or simply adding names to a list and using find/replace.

As an example-- in your macro template (where the macros are stored, as long as it isn't just in your Normal template), you could put a table. And then add names to individual table cells.

Cycle through the table cells, build your array of names... and then simply do a bunch of find/replace all based on how many names you have in your array. The table would get a little repetitive when you add all the "MacLauren" and "MacMahon"... but it becomes very easy to maintain. There are as many variations of what will happen with string manipulation as there are Mc Mac and O' names out there.

All you'll have to do when you realize something wasn't added to the list is add a new row and type in the name. A little extra processing to deal with all caps, and you won't have to look at the code again. Just the table.

My two cents.

[macropod]

Hi Frosty,

I agree that building a list would probably be needed for many cases. Even then, I've encountered situations in which some people wanted all of their multi-word surnames in proper case whilst others, with exactly the same multi-word surnames, objected to this and only wanted the first letter capitalized. Try coding for that!

[Frosty]

Yet another good point. That's when you really get into building a bit of an application. I would address that by adding a column to my table, and titling it "ASK?" ... and if it had "yes" or "true" ... then in the middle of my find/replace loop, I would force the user to positively choose on each instance found.

Any time you're dealing with names, it's fairly difficult to address all possibilities (forget the easy Mc scenarios... as soon as you start dealing with other proper names of products iPod, iPad, etc) there becomes no one-size-fits-all solution. So I always code towards separating the action (find/replace) from the data (which names, and when).

[calbais]

Wow, you guys!! I've started quite a discussion. :hi:

All I needed to do was to correct the case of people's names that I copy from online applications and paste into a form I made to print a copy of their application. A lot of people don't care what their name looks like but I'm a bit fussy so I decided to do something about it and I also wanted to learn how to program in VBA. Thanks to you guys, it is working just fine. I can see that it can get very complicated if you try to correct all variables but this code corrects most of what I want. As for putting all the names in a list - this form I made is also used by others in my office so there would be no way to keep it up to date. (They are not very computer literate!)

I want to thank you all for your interest and help!!

[fumei]

rruckus "fumei: as I said, Word WILL recognize mcdonald vs McDonald in the Spelling Capitalization suggestion which you can read the suggestions and replace the text using VBA."

I stand by my original comment: Word does NOT recognize this. It only recognizes what YOU tell it to recognize. Whether by some built up table, or what you puty in as Spelling Corrections. YOU have to determine and set up the logic. And, as Paul mentions, to fully be sure, there will ALWAYS be some validation required.

That being said, yes, once determined and set up, most cases can be handled. But never all. But hey, 99.8% is still 99.8%, right?

[macropod]

Here's a re-write I've done of the function. In addition to what the previous one does, mine also allows you to specify whether to retain certain words in lower-case form.
Function ProperCase(StrTxt As String, Optional Caps As Long, Optional Excl As Long) As String
'Convert an input string to proper-case.
'Surnames like O', Mc and hyphenated names are converted to proper case also.
'If Caps = 0, then upper-case strings like ABC are preserved; otherwise they're converted.
'If Excl = 0, selected words are retained as lower-case, except when they follow specified punctuation marks.
Dim i As Long, j As Long, k As Long, l As Long, bChngFlg As Boolean
Dim StrTmpA As String, StrTmpB As String, StrExcl As String, StrPunct As String, StrChr As String
StrExcl = " a , an , and , as , at , but , by , for , from , if , in , is , of , on , or , the , this , to , with "
StrPunct = "!,:,.,?,"""
If Excl <> 0 Then
StrExcl = ""
StrPunct = ""
End If
If Len(Trim(StrTxt)) = 0 Then
ProperCase = StrTxt
Exit Function
End If
If Caps <> 0 Then StrTxt = LCase(StrTxt)
StrTxt = " " & StrTxt & " "
For i = 1 To UBound(Split(StrTxt, " "))
StrTmpA = " " & Split(StrTxt, " ")(i) & " "
StrTmpB = UCase(Left(StrTmpA, 2)) & Right(StrTmpA, Len(StrTmpA) - 2)
StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
Next i
StrTxt = Trim(StrTxt)
'Code for handling O' names
For i = 1 To UBound(Split(StrTxt, "'"))
If InStr(Right(Split(StrTxt, "'")(i - 1), 2), " ") = 1 Then
StrTmpA = Split(StrTxt, "'")(i)
StrTmpB = UCase(Left(StrTmpA, 1)) & Right(StrTmpA, Len(StrTmpA) - 1)
StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
End If
Next
'Code for handling hyphenated names
For i = 1 To UBound(Split(StrTxt, "-"))
StrTmpA = Split(StrTxt, "-")(i)
StrTmpB = UCase(Left(StrTmpA, 1)) & Right(StrTmpA, Len(StrTmpA) - 1)
StrTxt = Replace(StrTxt, StrTmpA, StrTmpB)
Next
'Code for handling names starting with Mc
If Left(StrTxt, 2) = "Mc" Then
Mid(StrTxt, 3, 1) = UCase(Mid(StrTxt, 3, 1))
End If
i = InStr(StrTxt, " Mc")
If i > 0 Then
Mid(StrTxt, i + 3, 1) = UCase(Mid(StrTxt, i + 3, 1))
End If
'Code for handling names starting with Mac
If Left(StrTxt, 3) = "Mac" Then
If Len(Split(Trim(StrTxt), " ")(0)) > 5 Then
Mid(StrTxt, 4, 1) = UCase(Mid(StrTxt, 4, 1))
End If
End If
i = InStr(StrTxt, " Mac")
If i > 0 Then
If Len(StrTxt) > i + 5 Then
Mid(StrTxt, i + 4, 1) = UCase(Mid(StrTxt, i + 4, 1))
End If
End If
'Code to restore excluded words to lower case
For i = 0 To UBound(Split(StrExcl, ","))
StrTmpA = Split(StrExcl, ",")(i)
StrTmpB = UCase(Left(StrTmpA, 2)) & Right(StrTmpA, Len(StrTmpA) - 2)
If InStr(StrTxt, StrTmpB) > 0 Then
StrTxt = Replace(StrTxt, StrTmpB, StrTmpA)
'Make sure an excluded words following punctution marks are given proper case anyway
For j = 0 To UBound(Split(StrPunct, ","))
StrChr = Split(StrPunct, ",")(j)
StrTxt = Replace(StrTxt, StrChr & StrTmpA, StrChr & StrTmpB)
Next
End If
Next
ProperCase = StrTxt
End Function

[macropod]

Minor enhancement - change:

If InStr(Right(Split(StrTxt, "'")(i - 1), 2), " ") = 1 Then
to:

If InStr(Right(Split(StrTxt, "'")(i - 1), 2), " ") = 1 Or _
Right(Split(StrTxt, "'")(i - 1), 2) = Right(Split(StrTxt, "'")(i - 1), 1) Then

[correohs]

Question: I'd like to add some exception to the Proper Code such as CEO, UNICEF, NBA, ESPN, BMW, IBM as many other acronyms. Thanks!

[gmayor]

Paul's macro indicates that it is capable of preserving capitalisation of acronyms

If Caps = 0, then upper-case strings like ABC are preserved; otherwise they're converted.

See also the True title case macro on my web site at http://www.gmayor.com/word_vba_examples_2.htm This too will not affect acronyms already capitalised.

[correohs]

Thanks for the Reply. let say

i.e:
1) LCell.Formula = ProperCase(LCell.Formula, 0, 0) input IBM DB2 Version For Linux, UNIX, ANd WinDOWs Will be converted to IBM DB2 Version For Linux, UNIX, ANd WinDOWs
2) LCell.Formula = ProperCase(LCell.Formula, 1, 0) input IBM DB2 Version For Linux, UNIX, ANd WinDOWs Will be converted to Ibm Db2 Version for Linux, Unix, and Windows
3) LCell.Formula = ProperCase(LCell.Formula, 0, 1) input IBM DB2 Version For Linux, UNIX, ANd WinDOWs Will be converted to IBM DB2 Version For Linux, UNIX, ANd WinDOWs
4) LCell.Formula = ProperCase(LCell.Formula, 1, 1) input IBM DB2 Version For Linux, UNIX, ANd WinDOWs Will be converted to Ibm Db2 Version For Linux, Unix, And Windows

As you can see 2 and 4 are the best options but they can not preserved acronyms on upper case. number 1 and 3 preserved all upper cases.
I will look into the the link that you sent. once again thanks. If I find a solution I'll share it.

[correohs]

At the beginning of the function I added

Dim StrAllCaps As String
AcronymsCaps = 1 ' This control is not implemented yet. The idea is 0= follows the function rules 1= Acronyms on Upper-Cases as shown on StrExclCaps.
StrExclCaps = " IBM , CEO , UNICEF , C&C , UNIX , DB2 " ' keep adding as many acronyms as many you need to check.

At the very end of the function I added

For i = 0 To UBound(Split(StrExclCaps, ","))
StrTmpA = Split(StrExclCaps, ",")(i)
StrTmpB = UCase(Left(StrTmpA, 2)) & Right(LCase(StrTmpA), Len(StrTmpA) - 2)
StrTxt = Replace(StrTxt, Trim(StrTmpB), Trim(StrTmpA))

Next

ProperCase = Trim(StrTxt) ' This one is the very last sentence of the function


Following above sample the result is what I expected, it is correct.

LCell.Formula = ProperCase(LCell.Formula, 1, 1) input IBM DB2 Version For Linux, UNIX, ANd WinDOWs Will be converted to IBM DB2 Version For Linux, UNIX, And Windows

Feedback is always welcome.

[Juggler_IN]

If the input string is all in UPPERCASE then this function doesnt convert it to proper case.

How to convert a all upper case string to proper case?

[macropod]

Perhaps you should read what I posted in post #19, especially the comments in the first few lines of the code.

[gmayor]

If you are referring to Paul's function, then as I commented earlier, the function has a switch that will optionally preserve upper case to accommodate acronyms.

[Juggler_IN]

Got it. Thanks.