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

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

**Excel:** `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Title | Title case |
| the lord of the rings |  |
| a tale of two cities |  |
| WAR AND PEACE |  |
| pride and prejudice |  |
| of mice and men |  |
| the old man and the sea |  |

Formula in B2, filled to B7: `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")`

Result: The Lord of the Rings; A Tale of Two Cities; War and Peace; Pride and Prejudice; Of Mice and Men; The Old Man and the Sea

## 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.

## Other ways

- `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")," In "," in ")`: 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".

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/title-case-with-small-words-lowercase