Proper Case with a Range of Exceptions

reasonablenesscheck

New Member
Joined
Jul 2, 2009
Messages
42
Working with Excel's proper case function and artist's names can be such a pain. :mad:

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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
Yes, but it can be done in a formula:

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

reasonablenesscheck

New Member
Joined
Jul 2, 2009
Messages
42
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

New Member
Joined
Jul 2, 2009
Messages
42
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
Excel Workbook
AB
1ABCDABCD
2EFGHEFGH
3JOE SMITHJoe Smith
Sheet1
Excel Workbook
A
1ABCD
2EFGH
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Exceptions
 

reasonablenesscheck

New Member
Joined
Jul 2, 2009
Messages
42
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
Have you checked for extraneous spaces?
 

reasonablenesscheck

New Member
Joined
Jul 2, 2009
Messages
42
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...


Code:
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

New Member
Joined
Jul 2, 2009
Messages
42
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,627
Messages
5,445,571
Members
405,341
Latest member
AzureStoneDog

This Week's Hot Topics

Top