Proper Case with Exceptions

B

binar

Board Regular
Joined
Aug 20, 2006
Messages
71
Fellow Forum Members,

The code below converts a cell containing ALL CAPS text to Proper Case text in another cell:

=PROPER(A1)

How can the function above be modified so that it includes exceptions for words like:

"and"
"of"

These type of words need to stay as lower case in a PROPER case function. For example:

"Thelma and Louise"
NOT
"Thelma And Louise"


Any help will be greatly appreciated. Thanks.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
How many exceptions are there?

If it's only a few, like 3 or 4, it can be manageable with a formula..

If it is only a few, please list them.
 
Upvote 0
jonmo1 said:
How many exceptions are there?

If it's only a few, like 3 or 4, it can be manageable with a formula..

If it is only a few, please list them.
Click to expand...


What would be real cool is if it was possible to code a function that will allow me to add what the exceptions are as I go along discovering them.

However, if a function with such flexibility is impossible to code, I have the following four exceptions:

"and"
"of"
"the"
"a"

Thanks for any help.
 
Upvote 0
Here's a start with built in functions..

Code: 
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&PROPER(A1)&" "," The "," the ")," Or "," or ")," A "," a ")," And "," and ")," Hi "," hi ")," What "," what "))

All the spaces around each word are important...

I added Hi and What just for the heck of it.

But this is quite cumbersome.
And if in xl2003 or earlier, you can only do 6 exceptions.
You can get many more in xl2007 or higher.
But again, very cumbersome.


If you want something more dynamic, it will reqire VBA code
 
Upvote 0
jonmo1 said:
Here's a start with built in functions..

Code: 
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&PROPER(A1)&" "," The "," the ")," Or "," or ")," A "," a ")," And "," and ")," Hi "," hi ")," What "," what "))

All the spaces around each word are important...

I added Hi and What just for the heck of it.

But this is quite cumbersome.
And if in xl2003 or earlier, you can only do 6 exceptions.
You can get many more in xl2007 or higher.
But again, very cumbersome.


If you want something more dynamic, it will reqire VBA code
Click to expand...

Thanks a lot Jomno1,
I never would got to this point on my own. The function is over top! I have Excel 2007. I plan to study this function so that if in the future I need to add a few more exceptions I'll base it what you have already coded. Thanks Again. You are a very talented coder.
 
Upvote 0
Glad to help..

If you want to use VBA code, it's much easier to modify the list of exceptions...

Press ALT + F11 to open VBA window
Click Insert - Module
Paste the following
Rich (BB code): 
Option Explicit
Public Function MyProper(MyString As String, Optional Exceptions As Variant)
Dim c As Variant
If IsMissing(Exceptions) Then
    'Default list of exceptions
    Exceptions = Array("a", "and", "if", "the", "or")
End If
MyString = Application.Proper(MyString)
For Each c In Exceptions
    MyString = Replace(" " & MyString & " ", " " & c & " ", " " & LCase(c) & " ", , , vbTextCompare)
Next c
MyProper = MyString
End Function


Now you can use this as a formula in a Sheet

=MyProper(A1)

This will use the Default list of expeptions hard coded in the VBA function.
Which you can modify to your heart's content.

Or, you can put a list of exeptions in a range on the sheet say B1:B10
=MyProper(A1,B1:B10)

Or, you can even code the expeptions directly in the formula
=MyProper(A1,{"and","or","the","whatever","a"})


Hope that gives you some stuff to mull over..
 
Upvote 0
Jonmo1,
Thanks a million! This new code you have posted surpasses the awesomeness of your original code.

It's much more cooler! I am amazed with how the VBA makes it much more powerful. Thanks for sharing your coding talent.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Proper Data set from Date Range
Replies
3
Views
169
Fluff
Fluff
Z
  • Question
Center Across Selection Issue in Web App.
Replies
0
Views
98
Z01NK5
Z
Noid67
  • Question
Multiple worksheet change and Formula use
2
Replies
17
Views
324
mumps
mumps
B
  • Question
VBA to show and change list of names depending on selection
Replies
7
Views
456
mumps
mumps
E
  • Solved
Subroutine to Add Cell Comments Not Updating the Contents Correctly
Replies
7
Views
307
CephasOz
CephasOz

Forum statistics

Threads
1,214,651
Messages
6,120,738
Members
448,988
Latest member
BB_Unlv

Share this page

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