Find and replace text in Excel and CSV files

How to find and replace manually in Excel

The Find and Replace dialog

1. Press Ctrl+H, or go to Home › Find & Select › Replace.
2. Type the text in Find what and the new text in Replace with. Leave Replace with empty to delete the text.
3. Click Options >> to see the extra settings:
   • Within: Sheet or Workbook.
   • Search: By Rows or By Columns, which only changes the order Excel moves through the cells.
   • Look in: on the Replace tab this is fixed to Formulas, so Excel replaces inside formulas as well as typed values.
   • Match case and Match entire cell contents.
4. Click Replace All. Excel tells you how many replacements it made, after the fact.

To limit the change to one column, select that column first; otherwise the whole sheet is searched.

Wildcards and special characters

In Excel’s dialog, * stands for any number of characters and ? for a single character. Searching for “Pvt*” therefore matches “Pvt Ltd”, “Pvt. Limited” and anything else starting with Pvt. To find a real asterisk or question mark, put a tilde in front: ~* or ~?. To find a line break inside a cell, click in Find what and press Ctrl+J; the box looks empty but contains the break.

Things to watch

• Undo is the only safety net. Replace All gives no preview, and once you save and close, the old values are gone.
• Formulas get changed too. Replacing “2023” across a sheet can rewrite references or numbers inside formulas, not only the visible values.
• Short search terms catch more than expected. Replacing “Ltd” also changes “Ltd.” and words such as “Ltda” unless Match entire cell contents is ticked.

The SUBSTITUTE function

For a formula-based approach, =SUBSTITUTE(A2,"Pvt Ltd","Private Limited") returns the text with every occurrence replaced. SUBSTITUTE is case-sensitive, so it ignores “pvt ltd”, and you need to nest one SUBSTITUTE inside another for each spelling. A fourth argument replaces only a given occurrence. Do not confuse it with REPLACE, which works by position: =REPLACE(A2,1,3,"IN-") swaps the first three characters regardless of what they are.

Where this tool helps

Standardising company names. Vendor and customer lists collect “Pvt Ltd”, “pvt ltd” and “PVT LTD” for the same suffix. With Match case off, one replacement turns all of them into “Private Limited”, so filters and pivot tables group each company correctly.

Clearing placeholders before analysis. Exports often fill gaps with “N/A”, “-”, “NULL” or “none”. Averages and counts treat those as text. Replacing them with nothing, using “Whole cell only”, gives truly empty cells that formulas and the Fill blank cells tool understand.

Renamed codes. When a product line or branch code changes, such as “MUM-” becoming “BOM-”, one replacement updates every row of a price list or order export.

Removing a label from amounts. Values like “Rs 1,250” or “INR 980” can be cleaned by deleting the “Rs “ or “INR “ text, leaving a value that a number conversion can read.

Text from other systems. Accounting packages and old databases leave behind odd abbreviations or tags. Swapping them out before importing the data elsewhere saves a clean-up later.

Why it is handy for CSV files

Opening a CSV in Excel just to run Replace All has side effects. Excel reads “00123” as the number 123, may turn codes such as “3-4” into dates, and shows long IDs in scientific notation. Saving the CSV keeps those changes. This tool edits the values as they are in the file, so leading zeros, codes and dates come back exactly as they went in, apart from the text you replaced.

How the tool behaves

• Plain text, not patterns. Characters such as *, ?, (, ) and $ are matched exactly, so you never need an escape character.
• A count before you commit. As you type, a line such as “Will replace 4 matches of “pvt ltd” with “Private Limited” in 4 cells.” tells you what will happen. The button reads Replace all, or Delete matches when Replace with is empty.
• Anywhere in the cell or whole cell only, the second working like Excel’s Match entire cell contents.
• All columns or only the ones you choose, with the header row left alone unless you include it.
• No formulas to worry about. Values are replaced, and the file keeps its original format.

Examples