Spreadsheet Practice

How to use title case but keep small words lowercase in Excel

Formula (Excel and Google Sheets)

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")

Apply PROPER, then lowercase the small words with SUBSTITUTE, matching them with a space on each side so the first word stays capitalized.

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

PROPER capitalizes every word, including the small ones. Each SUBSTITUTE then swaps one small word back to lowercase. Searching for " Of " with spaces on both sides only matches the word in the middle of a title, so "Of Mice and Men" keeps its capital O, and words like "Offer" are never touched.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")

Result"The Lord of the Rings"

  1. A2"the lord of the rings"

    The value in A2.

  2. PROPER(A2)"The Lord Of The Rings"

    PROPER: Capitalizes the first letter of each word.

    • textA2"the lord of the rings"
  3. SUBSTITUTE(PROPER(A2)," Of "," of ")"The Lord of The Rings"

    SUBSTITUTE: Replaces matching text.

    • textPROPER(A2)"The Lord Of The Rings"
    • old_text" Of "
    • new_text" of "
  4. SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")"The Lord of The Rings"

    SUBSTITUTE: Replaces matching text.

    • textSUBSTITUTE(PROPER(A2)," Of "," of ")"The Lord of The Rings"
    • old_text" And "
    • new_text" and "
  5. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")"The Lord of the Rings"

    SUBSTITUTE: Replaces matching text.

    • textSUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")"The Lord of The Rings"
    • old_text" The "
    • new_text" the "
  6. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")"The Lord of the Rings"

    SUBSTITUTE: Replaces matching text.

    • textSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")"The Lord of the Rings"
    • old_text" A "
    • new_text" a "

In Google Sheets

The same formula works in Google Sheets: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a "). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")," In "," in ")The Lord of the Rings, A Tale of Two Cities, War and Peace…Add one SUBSTITUTE per extra word (in, on, to, for, with) to match your style guide.

Common mistakes

  • PROPER capitalizes after apostrophes, so "don't" becomes "Don'T". Fix it with SUBSTITUTE(…,"'T","'t").
  • A small word after a colon is lowercased too: "Star Wars: A New Hope" becomes "Star Wars: a New Hope", though most style guides capitalize it. Fix it with SUBSTITUTE(…,": a ",": A ").
  • Acronyms lose their capitals: "nasa" and "NASA" both become "Nasa".

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use title case but keep small words lowercase

In B2, convert the title to title case but keep "of", "and", "the" and "a" lowercase unless they are the first word. Fill down to B7.

Questions

Does Google Sheets have a title case function?

Only PROPER, which works the same; this SUBSTITUTE formula works in Google Sheets too.

How do I keep a word in capitals, like USB?

Add SUBSTITUTE(…,"Usb","USB") around the formula.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.