How to Fix CSV Headers with Spaces and Special Characters

A practical guide — Excel, Google Sheets, and the automatic way.

You know the file: " Client Name ", "EMAIL Address", "Invoice Total ($)". Headers with leading spaces, mixed case, and special characters. They break database imports, confuse mail-merge tools, and make formulas miserable.

What you usually want: clean, consistent headers like client_name, email_address, invoice_total. Here's how to get there.

What "clean" looks like

Before	After
`" Client Name "`	`client_name`
`"EMAIL Address"`	`email_address`
`"Invoice Total ($)"`	`invoice_total`

The pattern: lowercase, spaces and symbols become single underscores, no leading/trailing junk.

Method 1: Excel (Find & Replace)

Select the header row.
Ctrl+H → Find what: a single space, Replace with: _ → Replace All.
Repeat for other characters ((, ), $, -).
Select the row, go to Home → Change Case → lowercase (or use =LOWER(A1) and paste values).

Works, but it's fiddly and you have to repeat it for every file.

Method 2: Google Sheets (formula)

In row 2, enter this and drag across:

=LOWER(REGEXREPLACE(REGEXREPLACE(A1,"[^A-Za-z0-9]+","_"),"^_|_$",""))

Then copy → Paste special → Values only over the header row and delete the helper row.

Method 3: Python

import csv, re

def clean_header(h):
    h = re.sub(r'[^a-z0-9]+', '_', h.strip().lower())
    return re.sub(r'_+', '_', h).strip('_') or 'col'

with open('messy.csv', newline='', encoding='utf-8') as f:
    rows = list(csv.reader(f))

rows[0] = [clean_header(h) for h in rows[0]]

with open('clean.csv', 'w', newline='', encoding='utf-8') as f:
    csv.writer(f).writerows(rows)

The one-click way

Cleaning headers by hand every time gets old. CSV Cleaner normalizes every header to clean snake_case automatically — plus blank rows and encoding fixes. $9 once, yours forever.

See CSV Cleaner

More CSV fixes: remove blank rows · convert to UTF-8 · CSV won't import?