Proper Case with Exceptions

[binar]

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.

 

[Jonmo1]

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.

 

[binar]

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.

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.

 

[Jonmo1]

Here's a start with built in functions..

=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

 

[binar]

Here's a start with built in functions..

=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

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.

 

[Jonmo1]

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

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..

 

[binar]

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.

 

[Jonmo1]

Glad to help, thanks for the nice feedback.