How to Find and Replace in Excel Column (6 Ways)

In this article, we will learn about Excel Find and Replace in Column following a few easy methods. To make our task easier we can use the Find and Replace feature to easily locate anything like Value, Text, Formula, Format, etc. & Replace them.

Suppose we have a dataset of a company’s sales having Delivery Date, Region, Sales Person, Product Category, Product, and Sales Amount respectively in Columns A, B, C, D, E, F & G.

How to Find and Replace in Excel Column: 6 Easy Ways

1. Find and Replace in Column Using 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:

• Use the Keyboard shortcut CTRL + H or go to Home > Editing > Find & Select > Replace.

• Then in Find what box type what you want to find for replacing & in the Replace with box type what you want to replace with.
• In my dataset, I want to replace Chips with Crackers.
• To do so type the following as shown in the image below.

• If you want to Replace All at once select Replace All or If you want to Replace one by one just click Replace.
• I have selected Replace All.
• Upon clicking that MS Excel will show you a dialogue box having said the number of Replacements.

• Now all the Chips have been Replaced with Crackers.

2. Find and Replace in Column for Specific Format

In this method, we will learn how to Find and Replace in Columns for Specific Formats. Reading this we get to know how to Replace a specific Format with another Format.

Steps:

• First, open the Find & Replace dialogue box.
• Then to explore more click on the Options.

• Then to select a Format to Find, click on the First Format box & then a Drop Down menu will appear.
• From the Drop Down select Choose Format From Cell.

• Then a picker will appear & select a Cell that contains the format you want to Find.

• Now select another Format you want to Replace.
• Click the Format box below now to select.

• Now from the box select your desired Format that you want to Replace.
• I want to change from a dataset from Accounting Format to Currency Format
• Here I have selected Currency Format.
• Then Press OK.

• Now press Replace All.

• Upon this, you will have your dataset Replaced with Currency Format.

Read More: How to Find and Replace within Selection in Excel

3. Finding Formula to Replace in Column Using Find and Replace

Here I will demonstrate to you, how to Find & Replace a Formula in a Column.

Steps:

• Suppose you have applied the INDEX & MATCH formula in Column H.
• The formula is.

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

• Now as I have changed Column I & renamed it Product, the formula won’t give any output in the Column Region.

• Now to modify the formula accordingly go to Find & Replace dialogue box.
• Then type the following in the Find What & Replace With box to modify your formula.
• Then press Replace All.

• This will Find & Replace desired part of your formula & show you the output.

4. Find and Replace with Nothing in Excel Column

In this section, we will see how to Find anything within a Column & Replace it with Nothing.

Steps:

• First, select a Column where you want to Find & Replace your data.
• I have selected the Comment Column of my dataset.

• Then open the Find & Replace dialogue box.
• Here I want to remove Not Successful from the Comment Column. To do so type Not Successful in Find what box & keep the Replace With box.

• Upon selecting Replace All you will have your desired dataset.

5. Find a Line Break and Replace It

Now we will see how to Replace a Line Break in Excel.

Steps:

• First, select the Column of your dataset having Line Breaks.
• In my dataset, I have selected Column G having Line Breaks.

• Now open the Find & Replace dialogue box.
• Then type CTRL+J in the Find what box. This will look like a beeping full stop which is the symbol for Line Breaks.
• Keep the Replace With box.
• Then click Replace All.

• Now you will have your desired dataset.

6. Using Wildcards to Find and Replace in Excel

Here I will show you how to Find and Replace using the Wildcard Feature.

Steps:

• First, select a Column where you want to Find & Replace.

• Then open the Find & Replace dialogue box.
• Now we will use the Wildcard feature to Find & Replace.
• We can use Excel’s wildcard feature for several options including Finding and Replacing multiple characters. It can be done using the asterisk (*). For example ab* can locate the words “abraham” and “abram”.
• We can find single character  using the question mark (?). Peter and Piter are both found using P?ter.
• In our Column, we want to Find Cells having a Product Code starting with A & Replace it with Stock Out.
• So I will type A* in Find what box & type Stock Out in Replace with box.
• Then press Replace All.

• Now you will have your desired dataset.

Read More: How to Find And Replace Values Using Wildcards in Excel

Practice Worksheet

I have provided a Practice sheet for you. Try it.

Download Practice Workbook

Conclusion

Reading the article above we have learned in Excel Find & Replace in Excel using several criteria. Using Find & Replace feature really makes our task easier. Hope you have enjoyed reading this article. If you have anything to ask, please leave a comment.

Related Articles

• How to Find and Replace Asterisk (*) Character in Excel
• Find and Replace Tab Character in Excel
• [Fixed!] Excel Find and Replace Not Working
• Find And Replace Multiple Values in Excel
• How to Replace Special Characters in Excel
• How to Substitute Multiple Characters in Excel

<< Go Back to Find and Replace | Learn Excel