All Caps to Proper Case

[amerifax]

Does anyone out there having a solution for working with a name field. I have to convert all caps to proper. I have many of the typical problems associated with names. Here are a few:

Mc, Mac, Jr, I, II, one initial, two initial, LA, O'

There are so many I'm kind of drawing a blank tonight but I think you get the idea. This is how the data looks.

WALDORF ROBERT & VICKIE
ELKINS C
VETTER M M
FERBER-STUMPF JANELLE

It's a big project, but I was is hoping that maybe somebody has already done this dance and has it available.

 

[sanrv1f]

Have a look at PROPER function in excel help

 

[amerifax]

That I had. And it tells you straight out that it can't deal we conditions such as O'Neal, McDonald, I, II, la and so many more. Especially since the data fields, many, are 250,000 plus.

 

[Ruddles]

How about applying the PROPER function and then writing a piece of code to catch the exceptions?

Something like this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Function ProperName(ByVal argName As String) As String[/FONT]
 
[FONT=Fixedsys] Dim iPtr As Integer[/FONT]
 
[FONT=Fixedsys] ProperName = " " & Application.WorksheetFunction.Proper(argName) & Space(3)[/FONT]
 
[FONT=Fixedsys] For iPtr = 1 To Len(ProperName)[/FONT]
[FONT=Fixedsys]   Select Case True[/FONT]
[FONT=Fixedsys]     Case Mid(ProperName, iPtr, 2) = "Mc"[/FONT]
[FONT=Fixedsys]       ProperName = Left(ProperName, iPtr + 1) & UCase(Mid(ProperName, iPtr + 2, 1)) & Mid(ProperName, iPtr + 3)[/FONT]
[FONT=Fixedsys]     Case Mid(ProperName, iPtr, 2) = "O'"[/FONT]
[FONT=Fixedsys]       ProperName = Left(ProperName, iPtr + 1) & UCase(Mid(ProperName, iPtr + 2, 1)) & Mid(ProperName, iPtr + 3)[/FONT]
[FONT=Fixedsys]     Case Mid(ProperName, iPtr, 3) = "Mac"[/FONT]
[FONT=Fixedsys]       ProperName = Left(ProperName, iPtr + 2) & UCase(Mid(ProperName, iPtr + 3, 1)) & Mid(ProperName, iPtr + 4)[/FONT]
[FONT=Fixedsys]   End Select[/FONT]
[FONT=Fixedsys] Next iPtr[/FONT]
 
[FONT=Fixedsys] ProperName = Trim(ProperName)[/FONT]
 
[FONT=Fixedsys]End Function[/FONT]

Paste into a new general code module. Test in your worksheet like this: =ProperName("FRED MCMURRAY") or =ProperName(A1), or in the VBA Immediate window like this: ?ProperName("barack machussein o'bama").

Extra conditions can be added to the code as required.

Any good?

 

[willcass]

I had this trouble when I was working on a data cleaning exercise. I know its a bit crude but I used the find and replace tool to fix the obvious errors created by the PROPER function. Sorry I couldn't be of more use

 

[amerifax]

Great start for me. I'll work on it in the AM. Thanks for the help.

 

[amerifax]

willcass

I will upload the completed file when done. Also do you have a list on the problem name, such as Mc, O' and so many more.

 

[Trebor76]

Sorry, my bad

 

[amerifax]

To All

There are to ways to respond to a message.
1. Quick that is a button on a particular response.
2. As I'm doing here "Replay to Thread"

Question!
Method 2
If sent to "quick response", button on the actual message, well this alert the author. In other words will I not need to inter the name of the message's author. I do it times want to direct the response to a certain person. This would not be a personal responce but just giving the author a HEY.

I run this same board and I would like my board to respond to messages as I think it can

 

[Abgar]

Hi there,

Here is a macro version to do what you want:

Sub TitleCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Application.Proper(cell)
End If
Next
End Sub 

From what i have tested, the only thing it wont pick up correctly is Mc (i.e. McDonalds) and II (i.e. Edward Hammond II)

Just select all the cells you want to convert and then run the macro.

Hope it helps

Cheers