Proper Case with a Range of Exceptions

[reasonablenesscheck]

Working with Excel's proper case function and artist's names can be such a pain.

But I do have an accurate list of the way these artists names should appear.

For example: the artist EPMD should be all caps not Epmd.



I found this similar thread, but it isn't working properly because it doesn't allow for all caps names, it still forces proper case.

http://www.mrexcel.com/forum/showthread.php?t=475886


I have a named range of artists (named "Words") on a sheet called "Exceptions" which are the actual way the artist names should appear. (A:1 thru A:64,999)

I paste my data in A:1 through A:? on a sheet called "Change Case"

Is there a simple VBA that will run a proper case command and paste the names in B:1 thru B:? UNLESS the artist name appears on the exceptions list?

 

[Scott Huish]

Yes, but it can be done in a formula:

=IF(ISNUMBER(MATCH(A1,Exceptions!$A$1:$A$64999,0)),A1,PROPER(A1))

 

[reasonablenesscheck]

Thanks HOTPEPPER. I pasted that in B1, copied it down, and it does work for mixed case names, but it's maintaining the all caps output for certain artists not in the exception list. I think it's ignoring the Proper command if not found in the exceptions list when I want it to apply Proper case if not found in the exceptions list.

Can I do this in a VBA sub?

 

[reasonablenesscheck]

Just thinking out loud, but can't this sort of thing be done with a Do While Loop? Or a Do While Not?

Like Do Proper case while not artist = exception?

 

[Scott Huish]

Not sure what you mean, it's doing exactly what you asked, it is maintaining the entry exactly as is, if it is found in the exception list, otherwise it is applying Proper to it.

 

[Scott Huish]

Excel Workbook
	A	B
1	ABCD	ABCD
2	EFGH	EFGH
3	JOE SMITH	Joe Smith
Sheet1

Excel Workbook
	A
1	ABCD
2	EFGH
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Exceptions

 

[reasonablenesscheck]

Thanks for sticking with me on this HOTPEPPER.

I'll give you an example of what I am getting with my data.

<table x:str="" style="border-collapse: collapse;" width="336" border="0" cellpadding="0" cellspacing="0" height="297"><col style="width: 68pt;" width="91"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 68pt;" width="91" height="17">1st Offence</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">1st Team</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 B Free</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 Bad Mice</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 Birds, A Frog And A Vampire</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 BMF</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 Body's / Marathon</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 Boys</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2 Brothers On The 4th Floor</td> </tr> </tbody></table>

^That's my Exceptions List.

Here is my Input and Output.


<table x:str="" style="border-collapse: collapse;" width="493" border="0" cellpadding="0" cellspacing="0" height="317"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">2 BIRDS, A FROG AND A VAMPIRE</td> <td style="width: 48pt;" width="64">2 BIRDS, A FROG AND A VAMPIRE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2 BLACK 2 STRONG</td> <td>2 Black 2 Strong</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2 BLACK 2 STRONG & MMG</td> <td>2 Black 2 Strong & Mmg</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2 BMF</td> <td>2 BMF</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2 BOYS 1 GIRL</td> <td>2 Boys 1 Girl</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">2 BROTHERS ON THE 4TH FLOOR</td> <td>2 BROTHERS ON THE 4TH FLOOR</td> </tr> </tbody></table>
(BTW: My list is called Exception)

and I used this code.

=IF(ISNUMBER(MATCH(A1,Exception!$A$1:$A$64998,0)),A1,PROPER(A1))



It seems to be skipping certain entries in the exception list for no apparent reason. Sometimes digits don't exist for it to ignore exceptions, so I don't understand why it's not working.

 

[Scott Huish]

Have you checked for extraneous spaces?

 

[reasonablenesscheck]

Yep. I ran a trim All macro I found to remove all white spaces and I confirmed it manually on "2 Brothers On The 4th Floor" and 2 Birds, A Frog And A Vampire.

I put it in a Macro. At this point it's...

Sub properlikehammer()


LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:B" & LastRow).Formula = "=IF(ISNUMBER(MATCH(A1,Exception!$A$1:$A$19156,0)),A1,PROPER(A1))"


    
End Sub

 

[reasonablenesscheck]

By the way, at first I thought maybe special characters and numbers were causing the code to fail, but it is failing on others too.

For example ZZ Top is in my exceptions list. ZZ TOP is the input and ZZ TOP is the output.

Any reason why this isn't working??

Is it my syntax?

I am using an exact reference in the range, does it work with A:A instead?

Ughh.