Hi All
I am currently stuck on problem of converting surname to propercase…problem arises where name begins with O…eg o’rourke comes out as O’rourke…incorrect
it should be O’Rourke…
This is my formula so far any help greatly apprectiated…
@If(@Contains(surname;“'”;@Propercase(@Left(surname; “'”))+@Propercase(@Right(surname; “'”)); @Propercase(name))
Logic i am trying to apply is to split the name anything before or after ’ put in propercase…
thanks
Subject: @ProperCase Problem
Something like this should work (not tested though, so may need tweaking!):
name:=@ProperCase(YOURFIELD);
@If(
@Contains(name; "'");
@Do(
list:=@Explode(name; "'");
@Implode(@ProperCase(list); "'")
);
name
)
Subject: RE: @ProperCase Problem
Hi David
Thanks for your response…this is my formula now on view…but it still doesnt work…
name:=@ProperCase(surname);
@If(
@Contains(name; “'”);
@Do(
list:=@Explode(name; “'”);
@Implode(@ProperCase(list); “'”)
);
name
);
surname2 + " " + FirstName + MiddleName
Subject: RE: @ProperCase Problem
@Transform(_data; “x”; @Implode(@ProperCase(@Explode(x; “'”; 1)); “'”));
Subject: RE: @ProperCase Problem
hi all
thanks for all the help i have recieved on this…much appreciated…i have played around with code and this one works for surname view
name:=@ProperCase(surname);@If(@Contains(name; “'”);
@Do(list:=@Explode(name; “'”);@Implode(@ProperCase(list); “'”));name) + " " + @ProperCase(FirstName) + " " + MiddleName eg displays as O’Gorman,Lucy
Attempting now to apply to first name view eg Lucy O’Gorman…
but the following formula doesnt work this is what it displays Lucy O’gorman???
name:=@ProperCase(surname);@If(@Contains(name; “'”);@Do(list:=@Explode(name; “'”);@Implode(@ProperCase(list); “'”));name) ;
@ProperCase(FirstName) + “” + MiddleName +" " + @Propercase(name)
Subject: RE: @ProperCase Problem
Yes, you cant make your formula applicable to all the surnames. Probably you need to execute the formula only if the character (') exists in the surname, otherwise simply ignore it.
e.g.
finalname:=@If(@Contains(surname; “'”);@Implode(@ProperCase(@Explode(surname; “'”);“'”));surname)
Subject: @ProperCase Problem
Oh i see what you are saying my formula that fixes the O’s changes all others and messes them up eg McCarthy is now Mccarthy…ahhhhhh!!!
Subject: Micks, Dashes & Apostrophes
I use this:
T1:=CustomerLastName;
T2:=@If(@Contains(T1;“'”);@ProperCase(@Left(T1;“'”));@Contains(T1;“-”);@ProperCase(@Left(T1;“-”));“”);
T3:=@If(@Contains(T1;“'”);@ProperCase(@Right(T1;“'”));@Contains(T1;“-”);@ProperCase(@Right(T1;“-”));“”);
T4:=@Left(@LowerCase(T1);2);
T5:=@RightBack(@LowerCase(T1);2);
@If(T4=“mc”;“Mc”+@ProperCase(T5);T2=“”;@ProperCase(T1);@Contains(T1;“'”);T2+“'”+T3;@Contains(T1;“-”);T2+“-”+T3;“”)
It also handles names with hyphens in them and the good old Scottish ‘Mc’.
Subject: @ProperCase Problem
In the above case you know the pattern so you can split and make both the parts propercase. But, just out of curiosity like to know…how will you handle the surname like : McDonalds ?
Cheers !
Subject: RE: @ProperCase Problem
My only problem so far arises from surnames that begin with o…i am pulling them from address book and all other names eg mccarthy have been set up in correct format…McCarthy…
you think i only have to play around with existing formula to fix for first name??
Subject: RE: @ProperCase Problem
Oh i see what you are saying my formula that fixes the O’s changes all others and messes them up eg McCarthy is now Mccarthy…ahhhhhh!!!
Subject: RE: @ProperCase Problem
Yes, you cant make your formula applicable to all the surnames. Probably you need to execute the formula only if the character (') exists in the surname, otherwise simply ignore it.
e.g.
finalname:=@If(@Contains(surname; “'”);@Implode(@ProperCase(@Explode(surname; “'”);“'”));surname)
Subject: @ProperCase Problem
Joe,
I have gone through that problem myself. Years ago I wrote my own proper case routine to handle the anomalies that came up. I have included it below, just copy and paste it into the (Declarations) area of a script library. The big advantage of the code below is that you can modify the behavior of the routine to the names you are working with.
If you send me your e-mail I can send you my library of routines, of which ProperCase is just one.
Hope this helps.
Ron
Class ProperCase
'This class proper cases a string by converting the first letter of each word to uppercase.
'In addition, also converts the letter following these characters " .,&-(<[" and it attempts
'to proper case names beginning with "Mc" (i.e. McCormick) and names where the second
'character is an apostrophe (i.e. O'Malley).
'
'By default, words are defined as space delimited strings. The string "P.O. Box 9999-12345"
'consists of three words. If the hyphen is included as a work break character then the,
'string "P.O. Box 9999-12345" would contain of four words.
'
'Properties:
' Exact = String. Read-write. String of tilde (~) separated words which are always
' returned as defined here. Default: "~PhD~".
' Lower = String. Read-write. String of tilde (~) separated words which are always
' returned as lowercase. Default: "~aka~a/k/a~dba~de~".
' LowerFirst = String. Read-write. When True, converts sString to lowercase before
' converting to proper case. Default: True.
' Upper = String. Read-write. String of tilde (~) separated words which are always
' returned as uppercase. Default: "~DDS~II~III~IV~MD~PO~".
' UpperNext = String. Read-write. String which triggers the NEXT character to be
' converted to uppercase. Default: " .,&-(<[".
' WordBreak = String. Read-write. Each character in the defined string denotes a word
' break. Default: " ".
'
'Methods (Private):
'
'Methods (Public):
' New Instantiates the object and initializes default values.
' Result Pass a string to be converted to propercase. The converted string will be returned.
'
'Databases:
'
'Views:
'
'Examples:
' Dim oPCase As New ProperCase
' oPCase.LowerFirst = False
' oPCase.Result("santa claus") 'Returns "Santa Claus"
' oPCase.Result("jean-claude vanDamm") 'Returns "Jean-Claude VanDamm"
'
' oPCase.LowerFirst = True
' oPCase.Result("jean-claude vanDamm") 'Returns "Jean-Claude Vandamm"
' oPCase.Result("A. O'MALLEY") 'Returns "A. O'Malley"
' oPCase.Result("p.o. box 123") 'Returns "P.O. Box 123"
' oPCase.Result("mccormick spices") 'Returns "McCormick Spices"
' oPCase.Result("a. smith iii, esq.") 'Returns "A. Smith III, Esq."
'Class Objects:
Private bFirst As Boolean
Private sBreak As String
Private sExact As String
Private sLower As String
Private sNext As String
Private sUpper As String
'Properties:
Public Property Get Exact As String
Exact = sExact
End Property
Public Property Set Exact As String
sExact = Exact
End Property
Public Property Get Lower As String
Lower = sLower
End Property
Public Property Set Lower As String
sLower = Lower
End Property
Public Property Get LowerFirst As Boolean
LowerFirst = bFirst
End Property
Public Property Set LowerFirst As Boolean
bFirst = LowerFirst
End Property
Public Property Get Upper As String
Upper = sUpper
End Property
Public Property Set Upper As String
sUpper = Upper
End Property
Public Property Get UpperNext As String
UpperNext = sNext
End Property
Public Property Set UpperNext As String
sNext = UpperNext
End Property
Public Property Get WordBreak As String
WordBreak = sBreak
End Property
Public Property Set WordBreak As String
sBreak = WordBreak
End Property
'Constructor:
Sub New
'Set default values.
bFirst = True 'Convert to lowercase first.
sBreak = " " 'Word break character(s).
sExact = "~PhD~" 'Return exactly as is.
sLower = "~aka~a/k/a~dba~de~" 'Return in lowercase.
sNext = " .,&-(<[" 'Capitalize next character.
sUpper = "~DDS~II~III~IV~MD~PO~" 'Return in uppercase.
End Sub
'Destructor:
Sub Delete
End Sub
'Methods (Private):
'Methods (Public):
Public Function Result(Byval sString As String) As String
'Pass the string to be converted. The return string will be converted to propercase
'according the properties defined.
Dim iCntr As Integer
Dim iPos As Integer
Dim iTemp As Integer
Dim sChr As String
Dim sLast As String
Dim sNew As String
Dim sSuffix As String
Dim sTemp As String
Dim sWord As String
sNew = ""
If Me.LowerFirst Then sString = Lcase(sString)
'First compare the entire string against the special test cases (exact, all lower, and
'all upper). This test is mostly for names, a person with the last name of "de la Garza"
'may prefer it to be "De La Garza". Note that the first match wins.
sTemp = "~" & sString & "~"
If Len(sString) > 0 And Len(sTemp) <= Len(Me.Exact) Then
If Instr(1, Me.Exact, sTemp, 5) Then 'Special case words will be returned exactly as specified.
sNew = Mid(Me.Exact, Instr(1, Me.Exact, sTemp, 5) + 1)
sNew = Left(sNew, Instr(1, sNew, "~", 5) - 1)
sString = ""
End If
End If
If Len(sString) > 0 And Len(sTemp) <= Len(Me.Lower) Then
If Instr(1, Me.Lower, sTemp, 5) Then 'Word is to be returned all lowercase.
sNew = Lcase(sString)
sString = ""
End If
End If
If Len(sString) > 0 And Len(sTemp) <= Len(Me.Upper) Then
If Instr(1, Me.Upper, sTemp, 5) Then 'Word is to be returned all uppercase.
sNew = Ucase(sString)
sString = ""
End If
End If
'If the word (sString) was not formatted above, extract and test each word individually.
Do While Len(sString) > 0
'Extract the next word from the string. Words are delineated by the defined break
'characters. If multiple break characters are defined, the one that is found first
'will be used. If no break characters are found, the remaining string will be
'treated as a single word.
iPos = 0
sWord = ""
For iCntr = 1 To Len(Me.WordBreak)
sChr = Mid(Me.WordBreak, iCntr, 1)
iTemp = Instr(1, sString, sChr, 5)
If iTemp > 0 And (iPos = 0 Or iTemp < iPos) Then iPos = iTemp
Next
If iPos = 0 Then 'No break character, so treat the
sWord = sString ' remaining string as a word.
sString = "" 'Clear the string.
Else
sWord = Left(sString, iPos) 'Extract the next word and
sString = Mid(sString, iPos + 1) ' remove it from the string.
End If
If Len(sWord) = 1 And Instr(1, sWord, Me.WordBreak, 5) Then 'The "word" is simply a break character,
sNew = sNew & sWord ' so append it to the new string.
Else
'Save off any trailing characters (spaces, commas, periods, etc.) leaving just
'the "word" to use for comparisons and processing.
sSuffix = ""
For iCntr = Len(sWord) To 1 Step -1
sChr = Mid(sWord, iCntr, 1)
If sChr Like "[a-zA-Z]" Then
Exit For
Else
sSuffix = sChr & sSuffix
End If
Next
sWord = Left(sWord, iCntr)
sTemp = "~" & sWord & "~" 'Test the word against the spacial case lists.
If Instr(1, Me.Exact, sTemp, 5) Then 'Special case words will be returned exactly as specified.
sTemp = Mid(Me.Exact, Instr(1, Me.Exact, sTemp, 5) + 1)
sTemp = Left(sTemp, Instr(1, sTemp, "~", 5) - 1)
Elseif Instr(1, Me.Lower, sTemp, 5) Then 'Word is to be returned all lowercase.
sTemp = Lcase(sWord)
Elseif Instr(1, Me.Upper, sTemp, 5) Then 'Word is to be returned all uppercase.
sTemp = Ucase(sWord)
Else
iTemp = Len(sWord)
sLast = ""
sTemp = ""
For iCntr = 1 To iTemp
'Do the tests only if it is a lower case letter (they are the only
'characters that can be uppercased).
sChr = Mid(sWord, iCntr, 1) 'Extract the next character from the word.
If sChr Like "[a-z]" Then
If iCntr = 1 Or Instr(1, Me.UpperNext, sLast, 5) Then
'If it is the first character or the last character is one
'of those listed, uppercase the current character.
sChr = Ucase(sChr)
Elseif Len(sTemp) = 2 Then 'Test the last two characters processed.
'This propercases names such as McCormick.
If Right(sTemp, 2) = "Mc" Then sChr = Ucase(sChr)
'This propercases names such as O'Malley and D'Shea.
If Right(sTemp, 1) = "'" Then sChr = Ucase(sChr)
End If
End If
sLast = sChr
sTemp = sTemp & sChr
Next
End If
sNew = sNew & sTemp & sSuffix 'Append the word just processed to the new string.
End If
Loop
Result = sNew 'Return the new string.
End Function
End Class
Subject: RE: @ProperCase Problem
Hi Ron
Thanks for your help on this but i real newbie when it comes to script…the original formula i posted i imagined if i just manipulated it, it should work for the first name view…
as its working fine now for my second name view…
Subject: RE: @ProperCase Problem
Joe,
I understand your problem and your attempts to correct it. One of the first tasks assigned to me when I started with my current company, was to see if I could improve the performance of the company phone book.
The employee data was downloaded each night from the HR database, where the data is entered in all uppercase. Nearly every view column in the phone book had an @ProperCase() function programmed for that column, and some of the columns had complex code to handle the unique cases you are running into. The heavy, real-time processing is what was causing the application to run slow.
So, I’ve been there and through it. If you would like to hear the rest of the story, or would like some additional help, drop me a note.
Ron Reed