A similar =proper function

[Mcobra41]

I have a large sentance that is in all caps in one cell.
So it looks like this

"AS THE SON MICHAEL ARRANGES FOR THE BURIAL OF HIS FATHER AT HIS MOTHER'S
REQUEST, THE TOUCHING STORY OF HIS PARENT'S COURTSHIP UNFOLDS. IN THE DAYS OF
ARRANGED MARRIAGES, HE LEARNS THAT THEIR'S WAS THE FIRST BASED ON LOVE.
<br><br>"

I am trying to lower case everything, but the first letter of the sentances
and names, so in this case "As" and "Michael" and "In" would all stay
capitalized but everything else would lower case. I have about 30,000 cells
like this that the description is in all caps. Is there some sort of
function that would help fix this for me? or am I SOL? Thanks for any help
you can give me.

[Hollyo]

You will need to click on the cell that contains the text and insert the following:

=Proper(" "). The (" ") needs to be around your existing text.

Hope this helps.

Hollyo

[Dave Peterson]

Any chance you could copy that range of cells to MSWord, then you can change it
to Sentence case.

Format|Change case|Sentence case

Then copy|paste back to excel.

Mcobra41 wrote:
>
> I have a large sentance that is in all caps in one cell.
> So it looks like this
>
> "AS THE SON MICHAEL ARRANGES FOR THE BURIAL OF HIS FATHER AT HIS MOTHER'S
> REQUEST, THE TOUCHING STORY OF HIS PARENT'S COURTSHIP UNFOLDS. IN THE DAYS OF
> ARRANGED MARRIAGES, HE LEARNS THAT THEIR'S WAS THE FIRST BASED ON LOVE.
> <br><br>"
>
> I am trying to lower case everything, but the first letter of the sentances
> and names, so in this case "As" and "Michael" and "In" would all stay
> capitalized but everything else would lower case. I have about 30,000 cells
> like this that the description is in all caps. Is there some sort of
> function that would help fix this for me? or am I SOL? Thanks for any help
> you can give me.

--

Dave Peterson

[swatsp0p]

SOL is such a harsh condition!! However, there is no quick fix for your dilema, as you could use the "PROPER" function to change all except the first letter of each word to lower case. Not good.

"LOWER" changes ALL characters to lower case. Better, but not there yet.

Adding a helper column to first change all to lower case then another to Upper case the first letter. e.g.:

=LOWER(A1) in B1
=REPLACE(B1,1,1,UPPER(LEFT(B1,1))) in C1

returns: As the son michael arranges for the burial of his father at his mother's.....

However, Proper nouns remain lower case, as do the first words of additional sentences; still not there.

Spell check may catch some proper names (e.g. michael>Michael) but does nothing for sentence capitalization.

For a few cells, managable. For 30,000....YIKES!

Now, you're SOL. Otherwise, back to Mavis Beacon for speed typing?

Maybe someone has some VBA code to do this???

Good Luck