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?
 
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.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
AB
1ZZ TOPZZ Top
2ABCDAbcd
Sheet1
Excel Workbook
A
1ZZ Top
2*
3*
4*
5*
Exceptions
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top