How to Find and Replace in 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

In this method, I will show you how to Find and Replace anything in Columns using the Find & Replace dialogue box.

Steps

Press the keyboard shortcut CTRL + H or navigate to Home > Editing > Find & Select > 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.

Now all the Chips have been Replaced with Crackers.

Method 2 – Finding and Replacing Specific Format

Steps

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

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

Pick a cell containing the format you want to find.

Select the Format you want to Replace.

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

Press OK and then Replace All.

The selected data has been Replaced with Currency Format.

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

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.

This will result in the desired data set.

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.