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

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

**Google Sheets:** `=ARRAYFORMULA(PROPER(TRIM(A2:A7)))`

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Name (imported) | Clean name |
| maria lopez |  |
| JAMES O'NEIL |  |
| aiko   tanaka |  |
|  |  |
| lars ERIKSEN |  |
| chidi okafor |  |

Formula in B2: `=ARRAYFORMULA(PROPER(TRIM(A2:A7)))`

Result (spills 6 × 1): Maria Lopez; James O'Neil; Aiko Tanaka; ; Lars Eriksen; Chidi Okafor

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

## Other ways

- `=ARRAYFORMULA(IF(A2:A7="","",PROPER(TRIM(A2:A7))))`: The 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.

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

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/clean-a-whole-column-of-names-in-google-sheets