Capitalize first letter in each word, but NOT Proper

Something like this:

=UPPER(LEFT(A1, 1))&MID(A1, 2, 100)

Edit: Sorry, it won't work for multiple words in the cell as you write :/

Almost, but not quite; here's what was in the cell:

Please Login or Register  to view this content.

Here's what the formula gave:

Please Login or Register  to view this content.

Here's the desired output:

Please Login or Register  to view this content.

I'd also like to have this in a macro that would 1)Convert all existing text and 2)convert everytime data is entered.

SHG, I can't comment on the "RegExp" solution, but my attempts at the "plumbing" are admittedly NOT very pretty. Here's my latest try, with the MostlyProper macro in Module1, and the code below in my target worksheet:

Please Login or Register  to view this content.

Whatever I do I keep coming up with the same error,"Compile Error: Argument not optional" on the MostlyProper code. I would appreciate a little more hand-holding if you would be so kind.

My level of ignorance continues to astound me.

SHG, even with your hand-holding on the worksheet change event I wasn't able to get it to work. No error, just no result. If I changed the name of MostlyProper I got an error, so I know we were accessing the module, it just didn't do anything.

Leith, since I trust SHG's opinion when it comes to code, when he said a RegExp version would be prettier, I was excited to try your code. I pasted it into a standard module, then in the target worksheet I tried to do the event macro. But I ran into the original problem I had with SHG's code, namely that I couldn't figure out how to do the Worksheet Change Event routine. I tried what SHG supplied, substituting "FirstCap" for "MostlyProper", but that wouldn't work. Face it, I'm a lost cause.

Kelshaer,
Your code worked, right off the bat, the first time I tried it. No errors, no fuss, and it seems to be doing exactly what I want it to do. Unless Leith or SHG can point out the error of my ways regarding their macros, I'll run with yours.

Thanks,
John

SHG, I just noticed you updated your code in your first post. I didn't see that before. With that code, your second (the worksheet change event) works, so ignore my last post.

And now I've verified that Leith's code works, SHG's code works, and Kelshaer's code works. I call that being triply blessed.

For the most part the separate codes all produce the same results; "this is an FBI test" comes out as "This Is An FBI Test", but I've noticed this one difference:
For SHG and Kelshaer, if I key in "this is a TeSt", the result is "This Is A Test".
With Leith, the result is "This Is A TeSt".

I can't say Leith is wrong, and I can't say SHG and Kelshaer are right. It's simply a different result. I can see situations where I'd like one or the other, so I'm glad there are differences.

Thank you all for your help on this one.

John

I found that with Leith's code this case does not work: "test aBc" results in "Test aBc".
Would it be possible to get "Test ABc"? Respect all caps in the text, even if they are not in the first position of the word.
Also, with "this is a TeSt" or "thi sis a teSt" I get "This Is a TeSt" (it skips the "a").
I am working with version 2007.
I don't know exactly how Match works, I am reading the help and some examples now.
Thanks!

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.