🔁 Data Cleaning · 6 min read

Find and Replace in a CSV: 4 Ways That Work (2026)

To find and replace in a CSV, edit the values inside a parsed table rather than the raw text of the file. In Excel or Google Sheets, press Ctrl plus H and use Replace All. In the terminal, sed -i 's|old|new|g' data.csv does it without opening anything. In Python, df["status"].str.replace("N/A", "Unknown", regex=False) scopes the change to one column. The distinction matters because a CSV is not plain text: a field is allowed to contain commas, quotes and line breaks, so a replace that runs over raw characters can rewrite the structure of the file as well as the data.

Why a Raw Text Replace Can Break a CSV

RFC 4180 lets a field hold a comma, a double quote or a line break, as long as the whole field is wrapped in double quotes and any internal quote is doubled. That rule is the reason a CSV can carry a whole paragraph in one cell, and it is also the reason character-level editing is risky.

• Replacing a comma changes the number of columns in every row it touches. A field that used to be one value is now two.
• Replacing a quote can unbalance the quotes around a field, and everything after it is parsed differently. One stray edit reshuffles the columns for the rest of the row.
• Matching a common word hits it wherever it appears, including inside a quoted field that has nothing to do with the column you had in mind.

None of this means the job is hard. It means the edit belongs at the level of field values, where the tool knows what a value is. Every method below does that except sed, which is included because it is genuinely useful and worth using with eyes open.

Method 1: Excel

Excel finds and replaces within a selection, a sheet, or the whole workbook.

1. Press Ctrl plus H.
2. Type the Find what and Replace with values.
3. Open Options. Check Match entire cell contents if a search for N/A should not also rewrite N/A - pending.
4. Set Within to Sheet or Workbook, then click Replace All.

Two defaults catch people out. Match case is off, so the search is case-insensitive unless you tick it. And if you have a range selected when you open the dialog, Replace All is limited to that range, which is usually what you want but is worth knowing rather than discovering. Excel also stops at 1,048,576 rows, so a larger file is silently truncated on open before any replace runs.

Method 2: Google Sheets

The same Ctrl plus H opens Find and replace, and it adds one option Excel does not have: Search using regular expressions.

That regex mode runs on RE2, Google's regular expression engine. Common patterns work, and two features are simply absent: lookbehind and backreferences. A pattern that tries to look behind a currency symbol, or to reuse a captured group by number, will not run. When you need either, move the job to Python, where the re module supports both.

Sheets also lets you restrict the search to a range by selecting it first, and it will offer to search within formulas rather than values, which is a useful way to fix a formula typo across a sheet.

Method 3: The Terminal, With a Backup

sed rewrites a file in place and does not care about quoting at all. Use it on columns where the replaced text cannot contain a delimiter or a quote, and take a copy first.

cp data.csv data.csv.bak
sed -i 's|N/A|Unknown|g' data.csv

The pipe character is the delimiter above, which avoids escaping the slashes that appear inside dates and file paths. On macOS the same command needs an empty argument after -i, because the BSD version of sed requires it and the GNU version does not:

sed -i '' 's|N/A|Unknown|g' data.csv   # macOS / BSD

That difference is the single most common reason a copy-and-pasted sed command fails on a laptop. If sed is the wrong tool for the shape of your data, perl -i -pe 's|old|new|g' data.csv is the usual substitute and handles larger files more comfortably.

Method 4: Python, the Version That Knows About Columns

This is the method to use when the data has quoted fields and the change should only touch one column.

import pandas as pd

df = pd.read_csv("orders.csv", dtype=str)
df["status"] = df["status"].str.replace("N/A", "Unknown", regex=False)
df.to_csv("orders_clean.csv", index=False)

The regex=False argument is not decoration. pandas treats the search string as a regular expression by default, so a literal value containing a dot, a bracket or a plus sign matches something else or raises an error. Passing regex=False says plainly that you mean the characters you typed.

When you do want a pattern, re.sub is the tool, and it needs the backslashes written as backslashes:

import re

text = re.sub(r"\s+", " ", text)      # collapse runs of whitespace
text = re.sub(r"^\s+", "", text)      # strip leading space

Working on a parsed table also means the file structure takes care of itself. If a replacement introduces a comma or a quote into a value, the writer quotes that field on the way out, so the row still has the right number of columns.

Which Method Fits

Restrict the Change to the Column You Meant

Most find-and-replace accidents are not exotic. They are a word like Open or None that means one thing in a status column and something else in a notes column, rewritten everywhere because the tool was pointed at the whole file. Two habits avoid nearly all of it: select the column before you replace in a spreadsheet, and in code name the column explicitly instead of operating on the table.

It is also worth checking what you are about to change. A word you think is rare may be load-bearing in a column you have not read. The free CSV analyzer shows unique values and fill rates per column, so you can see how many rows a replacement will actually touch before you run it. For a broader pass over a messy export, the dirty data checklist puts replacement in the right order relative to duplicates, blanks and encoding fixes, and the duplicate removal guide is the next step if the same value arrives under several spellings.

Frequently Asked Questions

How do I find and replace in a CSV file?

Press Ctrl plus H in Excel or Google Sheets and use Replace All, or run sed -i 's|old|new|g' data.csv in the terminal, or use a pandas column with str.replace(..., regex=False). Edit values inside a parsed column whenever the file has quoted fields.

Why is a whole-file text replace dangerous on a CSV?

Because a CSV is not plain text. RFC 4180 allows a field to contain a comma, a quote or a line break inside double quotes, so a character-level replace cannot tell a value from syntax. Replacing a comma or a quote can change the column count of every row it touches.

Does Excel replace across the whole workbook?

The Within dropdown in the Find and Replace options offers Sheet or Workbook, so yes. Check Match case and Match entire cell contents first: both default to off, which means a search for banana also hits Banana and rewrites part of banana bread.

Can I use regular expressions in Google Sheets find and replace?

Yes, via the Search using regular expressions checkbox. It uses RE2, which supports common patterns but has no lookbehind and no backreferences. Where you need those, use Python's re module instead.

How do I replace values in only one column?

Select the column range before opening Find and Replace in a spreadsheet, or name the column in code: df["status"].str.replace(...). In SQL, an UPDATE with a WHERE clause on the same column. The scoping is what stops a common word being rewritten across every column.

How do I make a find and replace case-insensitive?

Excel and Sheets are case-insensitive by default, with Match case as an opt-in. In Python, re.sub takes re.IGNORECASE and pandas has a case argument. Decide it deliberately when normalising values for a merge, because Open and open will not join against a lookup table that lists only one of them.

How do I replace text in a huge CSV without opening it in Excel?

Use something that streams. sed -i edits in place, and Python's csv module processes one record at a time with flat memory use. DuckDB can rewrite a CSV from a SELECT with a replace function applied to one column. Excel is the wrong tool above 1,048,576 rows because it truncates silently.