Convert to Proper Case with Exceptions

Capt_Ron · Jun 21, 2010

I have found plenty of code to convert text in a range from UPPERCASE to Proper Case but I want to be able to exclude from the conversion any text that matches text found in a list on another worksheet (USA, TX, PD, UPS, a, an, at, in , on, etc.).

Can this be done referencing the list as a named range or would it be best to simply refer to the list of words by Sheet.Range?

Anyone have an example I could follow?

Ron

venkat1926 · Jun 21, 2010

I am giving the sample sheet below.
E1 to E8 are the pre defined data

A1 to A12 your input data
see formula in B1 which is copied down.

do you get the hang of it?

Book3

A

B

C

D

E

1

venkat

Venkat

USA

2

raja

Raja

Tx

3

raman

Raman

Oh

4

ashok

Ashok

a

5

USA

an

6

Tx

at

7

Oh

in

8

a

on

9

an

10

at

11

in

12

on

Sheet1

Capt_Ron · Jun 21, 2010

I am actually looking for a VBA solution of some sort. The code I've used in the past selected a target range of cells and converted to uppercase, so no problem. It's much tougher to go the other way.

The problem is abbreviations and acromyns normally in uppercase are changed as well. Example: UPS becomes Ups, USA goes to Usa, FBI becomes Fbi, etc.

Some words I just want changed to lower case....a, an, at, in, of, etc.

I'm willing to create lists on a separate sheet of the text I want to be uppercase and lowercase. I just haven't figured out how to put it together in a sub or event procedure.

I'm thinking something like:
If in the uppercase list, make uppercase
Else if in the lowercase list, make lowercase
Else if NOT in the Upper or lower case lists, make proper case.

Thanks,

Ron

mikerickson · Jun 22, 2010

This approach uses a single list of non-proper case words.
The column of Case Sprecific Words is a named range Words (=Sheet1!$G$2:$G$14).
The words in this range can have characters of mixed cases.
This range needs to be sorted ascending.

For single words, the spreadsheet formula (in B2) works:
=IF(ISNUMBER(MATCH(A2,Words,0)),LOOKUP(A2,Words,Words),PROPER(A2))

If you have cells with more than one word, the UDF CorrectCase works
=CorrectCase(A2,Words) is in C2

If needed, more punctuation can be added to the array in WordsOf.

Code:

Function CorrectCase(ByVal inputString As String, ByVal caseListRange As Range) As String
    Dim arrWords As Variant
    Dim i As Long, Pointer As Long
    On Error GoTo Halt
    Pointer = 1
    CorrectCase = inputString
    arrWords = WordsOf(inputString)
    
    For i = 0 To UBound(arrWords)
        Pointer = InStr(Pointer, inputString, arrWords(i))
        Mid(CorrectCase, Pointer) = CorrectCaseOneWord(CStr(arrWords(i)), caseListRange)
    Next i
    
Halt:
    On Error GoTo 0
End Function

Function WordsOf(inputString As String) As Variant
    Dim Delimiters As Variant, aDelimiter As Variant
    Dim arrResult As Variant
    
    Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf): Rem add to as needed
    
    For Each aDelimiter In Delimiters
        inputString = Application.Substitute(inputString, aDelimiter, Delimiters(0))
    Next aDelimiter
    
    arrResult = Split(inputString, CStr(Delimiters(0)))
    WordsOf = arrResult
End Function

Function CorrectCaseOneWord(inWord As String, caseListRange As Range) As String
    With caseListRange
        If IsError(Application.Match(inWord, .Cells, 0)) Then
            CorrectCaseOneWord = Application.Proper(inWord)
        Else
            CorrectCaseOneWord = Application.Lookup(inWord, .Cells)
        End If
    End With
End Function

In the below sheet,
The Excel formula in B2 and down is
=IF(ISNUMBER(MATCH(A2,Words,0)),LOOKUP(A2,Words,Words),PROPER(A2))

The UDF is in C2 downwards,
=CorrectCase(A2,Words)

Words is a named range for G2:G14.
Note that Words is sorted ascending.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=125>A<td width=125>B<td width=125>C<td width=25>D<td width=25>E<td width=25>F<td width=25>G</tr>
<tr><td align="center" bgcolor=#A0A0A0>1<td align="left" bgcolor=#FFFFFF>Input<td align="left" bgcolor=#FFFFFF>Excel formula<td align="left" bgcolor=#FFFFFF>UDF<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Case Specific Words</tr>
<tr><td align="center" bgcolor=#A0A0A0>2<td align="left" bgcolor=#FFFFFF>THE<td align="left" bgcolor=#FFFFFF>The<td align="left" bgcolor=#FFFFFF>The<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>a</tr>
<tr><td align="center" bgcolor=#A0A0A0>3<td align="left" bgcolor=#FFFFFF>UPS<td align="left" bgcolor=#FFFFFF>UPS<td align="left" bgcolor=#FFFFFF>UPS<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>AMA</tr>
<tr><td align="center" bgcolor=#A0A0A0>4<td align="left" bgcolor=#FFFFFF>DRIVER<td align="left" bgcolor=#FFFFFF>dRiVeR<td align="left" bgcolor=#FFFFFF>dRiVeR<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>an</tr>
<tr><td align="center" bgcolor=#A0A0A0>5<td align="left" bgcolor=#FFFFFF>MAY <td align="left" bgcolor=#FFFFFF>May <td align="left" bgcolor=#FFFFFF>May <td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>at</tr>
<tr><td align="center" bgcolor=#A0A0A0>6<td align="left" bgcolor=#FFFFFF>DRIVE<td align="left" bgcolor=#FFFFFF>Drive<td align="left" bgcolor=#FFFFFF>Drive<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>dRiVeR</tr>
<tr><td align="center" bgcolor=#A0A0A0>7<td align="left" bgcolor=#FFFFFF>IN<td align="left" bgcolor=#FFFFFF>in<td align="left" bgcolor=#FFFFFF>in<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>etc</tr>
<tr><td align="center" bgcolor=#A0A0A0>8<td align="left" bgcolor=#FFFFFF>A<td align="left" bgcolor=#FFFFFF>a<td align="left" bgcolor=#FFFFFF>a<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>FBI</tr>
<tr><td align="center" bgcolor=#A0A0A0>9<td align="left" bgcolor=#FFFFFF>TRUCK<td align="left" bgcolor=#FFFFFF>Truck<td align="left" bgcolor=#FFFFFF>Truck<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>in</tr>
<tr><td align="center" bgcolor=#A0A0A0>10<td align="left" bgcolor=#FFFFFF>OF <td align="left" bgcolor=#FFFFFF>of <td align="left" bgcolor=#FFFFFF>of <td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>of</tr>
<tr><td align="center" bgcolor=#A0A0A0>11<td align="left" bgcolor=#FFFFFF>STEEL<td align="left" bgcolor=#FFFFFF>Steel<td align="left" bgcolor=#FFFFFF>Steel<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>PD</tr>
<tr><td align="center" bgcolor=#A0A0A0>12<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>TX</tr>
<tr><td align="center" bgcolor=#A0A0A0>13<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>UPS</tr>
<tr><td align="center" bgcolor=#A0A0A0>14<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>USA</tr>
<tr><td align="center" bgcolor=#A0A0A0>15<td align="left" bgcolor=#FFFFFF>THE UPS DRIVER
MAY DRIVE IN
A TRUCK OF STEEL<td align="left" bgcolor=#FFFFFF>The Ups Driver
May Drive In
A Truck Of Steel<td align="left" bgcolor=#FFFFFF>The UPS dRiVeR
May Drive in
a Truck of Steel<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

Capt_Ron · Jun 24, 2010

Thank you for a very nice example. I like the use of the array and the delimiting characters. I'll work with this for a while and see what I can do with it. I appreciate your help.

Ron

dchieu · Dec 6, 2014

Hello,

I have attempted the UDF with the following exception range name, sorted...
=CorrectCase (textCell,excludeDB)

where excludeDB range is the following:
II
NY
NYTM

Input string= "NY TECH MEETUP (NYTM)"
Output = "Ny Tech Meetup (Nytm)"
Desired Output = "NY Tech Meetup (NYTM)"

However, it does not produce the desired results. Is there a possibility that the coding is slightly off?

Thanks, in advance, for any replies.

Peter_SSs · Dec 6, 2014

Welcome to the MrExcel board!

Could you give a bit larger set of sample data (say 8-10) and desired output and include (if it is possible with your data) things like
- any text that might include apostrophes or other punctuation.
- examples where something other than one of your excludeDB values is enclosed in parentheses.
- examples where the original text is not all upper case.

mikerickson · Dec 6, 2014

The UDF WordsOf strips punctuation. Parenthesis were not included when this was posted. Change WordsOf to

Code:

Function WordsOf(inputString As String) As Variant
    Dim Delimiters As Variant, aDelimiter As Variant
    Dim arrResult As Variant
    
    Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf, "(", ")"): Rem add to as needed

' ...
End Function

dchieu · Dec 6, 2014

Thanks for the replay Mike.

I'm a tad confused.
I've copied your updated lines (which seem to be same as original) as follows:

Function WordsOf(inputString As String) As Variant
Dim Delimiters As Variant, aDelimiter As Variant
Dim arrResult As Variant

Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf, "(", ")"): Rem add to as needed

My result is:
#NAME?

What am I missing?

Peter_SSs · Dec 6, 2014

dchieu said:
.. (which seem to be same as original) as follows:

Function WordsOf(inputString As String) As Variant
Dim Delimiters As Variant, aDelimiter As Variant
Dim arrResult As Variant

Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf, "(", ")"): Rem add to as needed

My result is:
#NAME?

What am I missing?

The original did not include the red text.

You need to have both of Mike's functions and use the CorrectCase function in your worksheet.

If that doesn't resolve your issue some samples as I asked for would be useful to me as I am investigating a slightly different method but would like to know what I have to deal with.

Convert to Proper Case with Exceptions

Capt_Ron

New Member

Excel Facts

venkat1926

Well-known Member

Capt_Ron

New Member

mikerickson

MrExcel MVP

Capt_Ron

New Member

dchieu

New Member

Peter_SSs

MrExcel MVP, Moderator

mikerickson

MrExcel MVP

dchieu

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Convert to Proper Case with Exceptions

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP, Moderator

MrExcel MVP

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock