Spreadsheet Practice

How to clean a whole column of names with one formula in Google Sheets

Google Sheets formula

=ARRAYFORMULA(PROPER(TRIM(A2:A7)))

Wrap TRIM and PROPER in ARRAYFORMULA so they run on the whole range: =ARRAYFORMULA(PROPER(TRIM(A2:A7))).

Result on the example
a 6-row list that spills from B2 in B2
Excel
See below

See it work

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

How it works

TRIM and PROPER normally work on one cell. ARRAYFORMULA makes them work on every cell of the range, so one formula in B2 fills the whole column, and new names are cleaned as soon as they are typed into the range.

=ARRAYFORMULA(PROPER(TRIM(A2:A7)))

Result{6 rows × 1 col} "Maria Lopez", "James O'Neil", "Aiko Tanaka", "", …

  1. A2:A76 rows × 1 col: " maria lopez", "JAMES O'NEIL ", "aiko tanaka", (empty), …

    A range: 6 cells in one column.

  2. TRIM(A2:A7){6 rows × 1 col} "maria lopez", "JAMES O'NEIL", "aiko tanaka", "", …

    TRIM: Removes extra spaces, leaving single spaces between words.

    • textA2:A76 rows × 1 col: " maria lopez", "JAMES O'NEIL ", "aiko tanaka", (empty), …
  3. PROPER(TRIM(A2:A7)){6 rows × 1 col} "Maria Lopez", "James O'Neil", "Aiko Tanaka", "", …

    PROPER: Capitalizes the first letter of each word.

    • textTRIM(A2:A7){6 rows × 1 col} "maria lopez", "JAMES O'NEIL", "aiko tanaka", "", …
  4. ARRAYFORMULA(PROPER(TRIM(A2:A7))){6 rows × 1 col} "Maria Lopez", "James O'Neil", "Aiko Tanaka", "", …

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaPROPER(TRIM(A2:A7)){6 rows × 1 col} "Maria Lopez", "James O'Neil", "Aiko Tanaka", "", …

In Excel

In Excel 365, the same formula without ARRAYFORMULA spills on its own: =PROPER(TRIM(A2:A7)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ARRAYFORMULA(IF(A2:A7="","",PROPER(TRIM(A2:A7))))spills 6 × 1The IF keeps empty rows empty. It matters more with open ranges like A2:A, and with functions that turn blanks into 0.

Common mistakes

  • Anything typed in the cells below B2 blocks the result with a #REF! error. Keep the output column empty.
  • An open range like A2:A runs on every row of the sheet. Use the IF version so empty rows stay truly blank.

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.

Clean a whole column of names with one formula

In B2, clean every name in A2:A7 with one formula: remove extra spaces and capitalize each word.

Questions

How do I make the formula grow with new rows?

Use an open range: =ARRAYFORMULA(IF(A2:A="","",PROPER(TRIM(A2:A)))).

How do I replace the original names?

Copy column B and use Paste special > Values only over column A, then delete the formula.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.