Proper Case conversion - solution (1 Viewer)

[Atomic Shrimp]

I had a large table of addresses and another of product details that were entirely in upper case; I needed to convert these to proper case, but the VB propercase function isn't quite clever enough;

[*]MCTAVISH should become McTavish but would actually become Mctavish and the customer would get angry.
[*]The 'OF' in 'KINGDOM OF BUTTER' would be captialised, when we wanted 'Kingdom of Butter'
[*]Postcodes; don't even talk to me about postcodes!

So I bashed out a quick case conversion function that does it very nearly correctly; there will always be an element of manual post-editing, but this function minimises it.

Public Function SmartCase(ConvertStr As String) As String
Dim mPointer As Integer
Dim mletter As Integer
Dim mtemploop As Integer
Dim mWord As String
Dim mchar As String

If ConvertStr = "" Then
    SmartCase = " "
    Exit Function
End If

mPointer = 0
'loop through entire string
Do
    mWord = ""
    'loop to get the next word
    Do
        mPointer = mPointer + 1
        mWord = mWord + Mid(ConvertStr, mPointer, 1)
    Loop Until (Mid(ConvertStr, mPointer, 1) = " ") Or (Mid(ConvertStr, mPointer, 1) = "-") Or (mPointer > Len(ConvertStr))
    'process the word:
    
    'Default word conversion:
    mletter = 0
    Do
        mletter = mletter + 1
        If InStr("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(mWord, mletter, 1)) > 0 Then
            mWord = (Mid(mWord, 1, mletter - 1)) & UCase(Mid(mWord, mletter, 1)) & LCase(Mid(mWord, mletter + 1, Len(mWord) - 1))
            mletter = Len(mWord) + 1
        End If
    Loop Until mletter > Len(mWord)
    
    'Conditional conversions:
    'Celts Mc~ and O'~
    If (Mid(mWord, 1, 2) = "Mc") Or (Mid(mWord, 2, 1) = "'") Then
        mWord = UCase(Mid(mWord, 1, 1)) & LCase(Mid(mWord, 2, 1)) & UCase(Mid(mWord, 3, 1)) & LCase(Mid(mWord, 4, Len(mWord) - 2))
        '(capitalise first and third letter, but not the rest)
    End If
    
    'Small words - do not capitalise at all unless it is the first word in the string
    If (InStr("A-An-And-At-In-Is-Or-The-Of-", Trim(mWord)) > 0) And ((mPointer - Len(mWord)) > 0) Then
        '(hyphens for Jack-in-the-Box - NB: you can customise this list)
        mWord = LCase(mWord)
    End If
    
    'Common abbreviations - capitalise entirely
    If InStr("Pb-Hb-Whs-", Trim(mWord)) > 0 Then
        '(hyphens as above NB: my abbreviations are for Paperback, Hardback and Warehouse, but you can customise them)
        mWord = UCase(mWord)
    End If
    
    'Words containing a numeric character - capitalise entirely
    For mtemploop = 0 To 9
        If InStr(mWord, Str(mtemploop)) > 0 Then
            mWord = UCase(mWord)
        End If
    Next
    
SmartCase = SmartCase & mWord
Loop Until mPointer > Len(ConvertStr)

End Function

Enjoy

 

[Jack Cowley]

Mike -

Thank you for sharing your efforts!

Jack

 

[Lyn Mac]

Mike -

Thanks for sharing.

Its people like you that makes the world (forum?) go round.[

]

I have your code in my repository for future
reference which I would need anytime soon.

Regards,

Lyn

 

[georget]

Can you help a Rookie?

Where do I place this code?

I have a contact table with names. I use these names on a form.

Thanks

 

[intrep11]

problem with code

i have cut and pasted this into a module but get a compile error on the lines the have >
what have i missed ?

and please can you tell me what the > dose as being a bit thick i am not sure what it is for

thanks
ND

 

[Mile-O]

You've missed out Mac as another Celtic prefix.

Also, you've only considered Irish names with the apostrophe after O. What about other names such as d'Angelo ?

One other thing: this line

If ConvertStr = "" Then

Probably me just being picky but it's faster to use the VB constant vbNullString than ""

If ConvertStr = vbNullString Then

 

[AncientOne]

An approach which allows for any given string to be properly converted is to put the names in a separate lookup table. If anyone is interested to receive further details, the code is in the Solutions database, which is on the Access CD.

 

[jhsurti]

I tried using this code in a form in the afterUpdate event of textboxes. works fine. BUT when i enter a multi-line text in a fiels for address, only the first line is properly converted but the remaining lines rae unaffected. How do u work around this scenario? I will attempt some coding as soon as I get time. But this is just for u to think about.