How to Find and Replace in an Excel Column (6 Methods)

Dataset Overview

Let’s assume we have a dataset of a company’s sales with columns A to G representing Delivery Date, Region, Sales Person, Product Category, Product, and Sales Amount, respectively.

excel find and replace in column

Method1 – Using the Find & Replace Feature

Steps

Press the keyboard shortcut CTRL + H or navigate to Home, select Editing, choose Find & Select and click on Replace.

In Find what box enter the text you want to find for replacement.
In the Replace with box enter the replacement text (e.g., replace Chips with Crackers).

Choose either Replace All to replace all instances at once or Replace to replace one by one.
If you select selected Replace All a dialog box will indicate how many replacements were done.

excel find and replace in column

All the Chips have been Replaced with Crackers.

excel find and replace in column

Method 2 – Finding and Replacing Specific Format

Steps

Open the Find & Replace dialogue box.
Click on the Options to explore additional settings.

excel find and replace in column

To find a specific format, click the First Format box and select Choose Format From Cell.

excel find and replace in column

Pick a cell containing the format you want to find.

excel find and replace in column

Select the Format you want to Replace.

excel find and replace in column

Click the Format box and choose your desired replacement format (e.g., changing from Accounting Format to Currency Format).

excel find and replace in column

Press OK and then Replace All.

excel find and replace in column

The selected data has been Replaced with Currency Format.

excel find and replace in column

Method 3 – Replacing Formulas in a Column

Steps

Suppose you’ve applied the INDEX & MATCH formula in Column H:

=INDEX(B6:E15,MATCH(G6,E6:E15,0),1)

Explanation:

B6:E15 is my data Range for the INDEX function.
G6 is the reference Cell.
E6:E15 is the reference Column.
0 indicates an exact match, and 1 refers to the first column in the data range.

This will give you output in column H for corresponding data in Column G.

Change Column I & rename it Product, the formula won’t give any output in the Column Region.

To modify the formula:
- Go to the Find & Replace dialogue box.
- Enter the necessary changes in the Find What and Replace With boxes.
- Press Replace All to update the formula and see the output.

Method 4 – Find and Replace with Nothing

Steps

Select the column where you want to find and replace data (e.g., the Comment Column in your dataset).

excel find and replace in column

Open the Find & Replace dialogue box.
Enter Not Successful in Find what box.
Leave the Replace With box empty.
Click Replace All to remove all instances of Not Successful.

excel find and replace in column

This will result in the desired data set.

excel find and replace in column

Method 5 – Replacing Line Breaks

Steps

Select the column with line breaks (e.g., Column G in your dataset).

Open the Find & Replace dialogue box.
Enter CTRL+J (represents a line break) in the Find what box.
Keep the Replace With box empty.
Click Replace All to replace line breaks.

Method 6 – Using Wildcards for Find and Replace

Steps

Select the Column where you want to Find & Replace.

Open the Find & Replace dialogue box.
Use Wildcards:
- Asterisk (*) finds and replaces multiple characters (e.g., ab* matches abraham and abram).
- Question mark (?) finds single characters (e.g., P?ter matches both Peter and Piter).
To find cells with a product code starting with A and replace them with Stock Out:
Enter A* in Find what box.
Enter Stock Out in the Replace with box.
Press Replace All.

Note: Use the tilde character (~) before asterisks or question marks if needed (e.g., “~*” or “~?”). For instance, if you want find Cells having asterisks, you have to enter ~* in the Find what box. For finding Cells that contain question symbol, use ~? in the Find what box.