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]

If ZZ TOP is in your exceptions list, it should return ZZ TOP because you don't want that Proper Cased, per your original explanation, if you wanted it to be Proper Cased, you should take it out of your Exception list.

 

[mikerickson]

I'm not sure what, in the OP link, isn't working for you.
If you have your oddly cased words in the named range Words, the formula
=CorrectCase(A1, Words) will change all the words in A1 to Proper Case unless they are in the list Words.

If you have two artist where the correct case is not consistant e.g. "BOB Smith" "Joe bob Jones", that formula won't work

 

[reasonablenesscheck]

Sorry I wasn't clear, allow me to clarify.

Using ZZ Top as an example:

If you run standard proper case as-is, to input "ZZ TOP", the output is "Zz Top" which is wrong. The bearded men would be mad at us for displaying their name like that.

Instead, I want "ZZ Top" to be the output because it is in my exceptions list. All artists in my exceptions list are the proper way to output the text. Sometimes it's all caps. Sometimes it's got Special characters like "2 Dogs, 2 DJ's and a Cat", and sometimes it's already in proper format like "Madonna"

So I want it to do proper case, unless the exact text resides already on the exceptions list even if on the exceptions list it's already in standard proper case.

 

[reasonablenesscheck]

FYI: If this is an issue, I'm using Excel 2002. Not 2007.

it shouldn't be, but you never know.

mikerickson, thanks for chiming in on this one as you were my original reference. I'll revisit your code, but I'm no longer using words as a named range for this code, is that the problem?

Instead I'm referring to a range on a sheet called Exceptions, which might be a confusing name since some are already in proper case, but its using the range name regardless.

 

[mikerickson]

If you have your list of full non-proper names in column D, e.g.

ZZ Top
k.d. lange


Then =IF(ISNA(MATCH(A1,D:D,0)), PROPER(A1), VLOOKUP(A1,D:D,1,FALSE))

will return the desired case of what is in A1.

The linked code was where every instance of a word was to be other than proper (e.g. "md" is always MD")
Using the code from the link, you could change "k.d. Lange" to "k.d. lange" but "fred lange" would be returned as "Fred lange" and "K.D.R.X. would be returned as "k.d.R.X."
That problem was word based, the current issue is name based.

 

[reasonablenesscheck]

If you have your list of full non-proper names in column D, e.g.

ZZ Top
k.d. lange


Then =IF(ISNA(MATCH(A1,D:D,0)), PROPER(A1), VLOOKUP(A1,D:D,1,FALSE))

will return the desired case of what is in A1.

The linked code was where every instance of a word was to be other than proper (e.g. "md" is always MD")
Using the code from the link, you could change "k.d. Lange" to "k.d. lange" but "fred lange" would be returned as "Fred lange" and "K.D.R.X. would be returned as "k.d.R.X."
That problem was word based, the current issue is name based.

Thanks, I see what you are saying.

I basically should remove any names that are already in proper case from my list and the code will work. But if it's already proper in my list I'm out of luck.

This proposes a new problem... Removing any names that are already in proper case from my list so it becomes an exceptions list in the true sense of the word exceptions.

So I should test my list for proper case, and if its proper, delete it. Then use that as my new list from now on.

Hmm... Testing for proper case, and deleting... Any ideas?

 

[Scott Huish]

It sounds like you want to change the value that you typed in to match the exception list, if it is there, otherwise you want to proper case it.

This is not what you said initally.

This is a slightly different version of what mikeerickson posted. You should not have to remove your proper cased entries from your Exceptions list, just have there what you want to maintain exactly.

To accomplish this, try this:

=IF(ISNUMBER(MATCH(A1,Exceptions!A:A,0)),LOOKUP(A1,Exceptions!A:A),PROPER(A1))

Excel Workbook
	A	B
1	ZZ TOP	ZZ Top
2	ABCD	Abcd
Sheet1

Excel Workbook
	A
1	ZZ Top
2	*
3	*
4	*
5	*
Exceptions

 

[reasonablenesscheck]

Sorry I took so long to follow up with this thread. I figured out what I was doing wrong, it was something with me putting the data on multiple sheets. Once I tried all the data on one sheet it worked like a charm but ONLY AFTER I identified which ones were already in proper case. To do that, I used this code...


=EXACT(A1,PROPER(A1))
Pasted in A1 and Drag Down

After Identifying all the "FALSE" for this, I sorted by that and removed the ones that were already in proper case. Then the original code worked like a charm!

RESOLVED!

Thank you guys so much, I couldn't have done it without your help.