Jeff,
I have worked up a pretty good vbscript that deals with about everything except
capitalizing "PHD" at the end of something like this:
dr. somegood operator, phd > Dr. Somegood Operator, Phd
dr. somegood operator,ii, phd > Dr. Somegood Operator, II, Phd
123 caNal StreeT, suite iii > 123 Canal Street, Suite III
mr. patrick h. o'mally > Mr. Patrick H. O'Mally
Now you could rework it to be called by WinUdf or you could run it with
RScripter Plugin. The Caveat with the Plugin is it is not well suited to Row
by Row processing because of the repeated calls to the plugin, but if it is
called once every few seconds or longer, then it will probably work just fine..
Or
You could use the program logic and recreate something in Delphi or something
else to make a straight UDF.
Anyway Here is RbProperCase.vbs,
'Option explicit
'msgbox CapitalizeValue(WScript.Arguments(0))
'wscript.quit()
Function CapitalizeValue(sStringIN)
Dim i
Dim s
Dim sNewName
Dim sChar
Dim arString
Dim bIsCapital
Dim bCapitalizeNext
'Get rid of:
' leading or trailing blanks,
' Double Spaces, space comma space
' Existing Caps
sStringIN = Replace(LCase(Trim(DropDblSpace(sStringIN))),",", " , ")
' replace comma with space comma space to preserve comma
if sStringIN = "" then
CapitalizeValue = sStringIN
end if
bCapitalizeNext = true
For i=1 to Len(sStringIN)
sChar = Mid(sStringIN,i,1)
if (bCapitalizeNext = true) then
if IsLetter(sChar) = true then
sChar = UCase(sChar)
bCapitalizeNext = False
else
bCapitalizeNext = True
end if
else
if IsLetter(sChar) = false then
bCapitalizeNext = true
end if
end if
sNewName = sNewName & sChar
Next
' Check for Roman Numerals as Last element of sNewName
arString = Split(sNewName," ")
if ubound(arString) > 0 then
for i = 0 to ubound(arString)
arString(i) = Trim(arString(i))
if isRoman(arString(i)) = True then
arString(i) = ucase(arString(i))
end if
next
for i = ubound(arString) To 0 Step - 1
If arString(i) <> "" then
CapitalizeValue = arString(i) & " " & CapitalizeValue
End if
Next
else
CapitalizeValue = sNewName
end if
CapitalizeValue = Replace(CapitalizeValue,",",", ")
end Function
function DropDblSpace(sStr)
Dim s
Dim r
s = Chr(20) & Chr(44)
r = Chr(44)
while InStr(sStr,s) > 0
sStr = Replace(sStr,s,r)
wend
While Instr(1,sStr," ") > 0
sStr = Replace(sStr, " ", " ")
wend
DropDblSpace = sStr
end function
function IsLetter(sChar)
Dim i
i = CInt((Asc(sChar)))
IsLetter = False
if ((i >=65) and (i <=90)) Then
IsLetter = true ' Upper case letters
End if
if ((i >=97) and (i <=122)) Then
IsLetter = True ' Lower case letters
End if
end function
Private Function IsRoman(ByVal sStr)
Dim i
Dim s
IsRoman = TRUE
s = Trim(UCase(sStr))
for i = 1 to len(s)
if inStr(1, "MDCLXVI", mid(s,i,1)) = 0 then
isRoman = False
exit For
end if
Next
End Function
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[EMAIL PROTECTED]>
Sent: Tuesday, December 07, 2004 10:20 AM
Subject: [RBG7-L] - Re: Label Text Formatting
> For shipping labels, I convert everything to upper case which is simple to
do,
> use a nice looking font, usually in bold and am satisfied with the results.
Looking at most shipping labels such as Fed Ex, Dell, etc. they seem to do the
same thing, i.e. all capital letters.
>
> For packing lists, it would be nice to have the usual mixed capital and lower
case. This proves to be difficult for 100% accuracy without human
intervention. You can parse the address data for spaces and periods and
capitalize everything that follows a space or period. This would catch
probably 99% of address information, but would not catch your "Iii" example
below. I do not know how you would manage a 100% effective logic system for
that.
>
>
> -------------- Original message --------------
>
> To All,
>
> For those of you who know about my endeavors with the Lapinator I am happy to
say that Solutions catalog sold out 1400 in five days this week, our sales are
up 700%, and PC magazine will be running a 4 star review in the 12/28 issue due
out 12/14!
>
> To process orders I have written a database that loads PayPal, credit card,
and Amazon emails, reads them, and creates a label and a packing slip.
Surprisingly, many people enter their address in all caps, all lower case and
combinations in between.
>
> So I tried some formatting with ICAP2 and its amazing that, just at that
point, all of the exceptions then come flying in. For example, John Alexander
Iii, R.w. Barrett, 113 North Ave N.e.
>
> Has anyone dealt with this issue and come up with a satisfactory solution?
>
> Jeff Ward
