[Solved] Automatically capitalize cell content like title

[WolfAmri]

Hi everyone!
Please help, I have a spreadsheet for footage files with 4000 rows. Each row contains a title, description, and keywords. Not being native English, I didn't format the title grammatically correctly. Now I have to change that.
I know about the formula "PROPER()", but that would just capitalize the first letter of every word, which is not grammatically correct.

My procedure now is:

• - copy the title

• - go to a website that auto-formats the title (https://capitalizemytitle.com)

• - paste it there

• - copy it from there

• - go back to the spreadsheet and replace the original by pasting it

As you can imagine that is pretty cumbersome for 4000 rows. So I wonder: is there any automation I can do, to have cells convert to grammatically correct titles?

Thanks in advance for any help!

[Zizi64]

Do you want to capitalize all of the letters, or you want to use Small Caps formatting property?

[WolfAmri]

Thanks, I would need it grammatically correct.

For example PROPER() would create
A Fox Is Red And Not Green
But correct (according to dedicated websites) would be:
A Fox Is Red and Not Green

[MrProgrammer]

I would need it grammatically correct.

OpenOffice does not have that feature. The requirement is complex because OpenOffice supports more than 40 languages, and grammar rules differ among them.

[WolfAmri]

I would need it grammatically correct.

OpenOffice does not have that feature.

Too bad. Thanks for your help though.

[gpgrego]

You can try to implement that function with a custom macro.
See this example.

[WolfAmri]

Wow! Thank you so much. I looked at the code and tried to understand, but didn't. However, I just converted all the titles with your help and copy/pasted it into the other document. Very much appreciated. You saved me a huge lot of time!!!

[Zizi64]

Code: Select all

    xSearch = " Up"
    iPos = InStr(MyTitleCase, xSearch)
    if iPos > 0 then
     sResult = Replace(sResult, xSearch, " Up")
    end if

    xSearch = " Up "
    iPos = InStr(MyTitleCase, xSearch)
    if iPos > 0 then
     sResult = Replace(sResult, xSearch, " up ")
    end if

(Sorry, my native language is not English.)
The word "Up" at the end of a sentence must be started with uppercase letter "U" in a Title, or is it a typo in your macro code?

[Villeroy]

Is there any Python library which can do this correctly?
Indeed, there is: https://pypi.org/project/titlecase/

[Lupp]

I will act as "The Rebel" in this case:

Imo there isn't legitimate authority concerning the orthography of titles.
It's the same in many different cases where teaching and "guides" and whatever pretend to know how something "must" be done. Nowadays software comes in with specifically silly ways of "proof reading", and of text manipulation.

Any language developed to its current states (plural!) and will not stop to develop because somebody wrote a book about its rules. This is very much true for English as a global language of understanding ("Lingua Franca").

Take pride in writing titles the way you think it's good and clever! Take part this way in creating the next -and better- standard.

....And start to obey standards (SI units, ISO concerning numbers and dates, ...) where they are really useful and well considered by experts.

[WolfAmri]

I will act as "The Rebel" in this case:

Imo there isn't legitimate authority concerning the orthography of titles.
It's the same in many different cases where teaching and "guides" and whatever pretend to know how something "must" be done. Nowadays software comes in with specifically silly ways of "proof reading", and of text manipulation.

Any language developed to its current states (plural!) and will not stop to develop because somebody wrote a book about its rules. This is very much true for English as a global language of understanding ("Lingua Franca").

Take pride in writing titles the way you think it's good and clever! Take part this way in creating the next -and better- standard.

....And start to obey standards (SI units, ISO concerning numbers and dates, ...) where they are really useful and well considered by experts.

Thanks for your opinion, but I'm afraid I have to use these standards because they are requested.

[RoryOF]

Careful proof-reading, which should be standard for any document, ought find any irregularities so you can correct them.

It is _your_ responsibility to have the document correct before you submit it; you should not rely on any program to do your thinking for you.

[WolfAmri]

Is there any Python library which can do this correctly?
Indeed, there is: https://pypi.org/project/titlecase/

Thank you!!!