Formula for converting Paragraph UpperCase to Paragraph Sentence Case

[binar]

Fellow Forum Members,
I have tested the five formulas listed below and none of them are able to successfully convert a paragraph of UPPERCASE text over to a paragraph of Sentence Case text. In other words, I need only the first text letter starting a sentence to be UPPERCASE for every sentence within a paragraph:

#1- SUBSTITUTE(LOWER(C2),CHAR(CODE(C2)),UPPER(CHAR(CODE(C2))),1)
#2- UPPER(LEFT(C2,1))&MID(LOWER(C2),2,999)
#3- left(A1) & lower(right(A1,len(A1)-1))
#4- UPPER(LEFT(A1)) & LOWER(MID(A1, 2, LEN(A1)-1))
#5- UPPER(LEFT(C4,1)) & LOWER(RIGHT(C4,LEN(C4)-1))

I hope someone out there can help everyone out in this forum by developing a formula which will accurately convert a paragraph over to Sentence Case because none of the formulas I have listed above seem to work right. Thanks in advance for your help.

 

[István Hirsch]

This seems to work:

=SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(A1," ","ZZZ"),".ZZZ",". ")),"zzz"," ")

 

[anthonya2369]

All those formulas would resolve a sentence but not a paragraph form. If you are interested in VBA and making your own formula to do this. The only other issue I would see is proper names, cities, and other capital words that may be in the middle of a sentence. Such as: "I went to the movies with Mr. Smith in Denver, Colorado" would read: "I went to the movies with mr. Smith in denver, colorado"

 

[anthonya2369]

Your formula works great except for other punctuation such as commas, semicolons, etc. I would recommend the following to resolve this:

=SUBSTITUTE(SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(A1," ","ZZZ"),".ZZZ",". ")),"zzz"," "),"Zzz"," ")

This still does not resolve the proper names, cities, and other capitalization in the middle of a sentence.

 

[István Hirsch]

It would be a big help if the OP gave some representative data rather than a list of formulas that do not work.

 

[István Hirsch]

The formula in post #2 works better if "ß" is selected as the temporary replacement character:

=SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(A1," ","ß"),".ß",". ")),"ß"," ")

 

[binar]

Thank you anthonya2369 and István Hirsch for your posts to this thread. I got positive results. In other words, I am now able to convert an uppercase paragraph to sentence case which is something I was not able to do before with any of the formulas I initially posted. Thank again and my hope is this thread will help someone else in the future.

 

[István Hirsch]

You are welcome, thanks for the feedback.