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

In this article, we will know 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 & Sales Amount respectively in Columns A, B, C, D, E, F & G.

excel find and replace in column


Download Practice Workbook


6 Easy Ways to Find and Replace in Excel Column

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.

excel find and replace in column

  • Now all the Chips have been Replaced with Crackers.

excel find and replace in column

Read More: How to Find and Replace Values in Multiple Excel Files (3 Methods)


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.

excel find and replace in column

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

excel find and replace in column

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

excel find and replace in column

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

excel find and replace in column

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

excel find and replace in column

  • Now press Replace All.

excel find and replace in column

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

excel find and replace in column

Related Content: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)


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:

=INDEX(B6:E15,MATCH(G6,E6:E15,0),1)
Explanation: Here B6:E15 is my data Range for the INDEX function. Then Cell G6 is the reference Cell & E6:E15 is the reference Column in my data Range. 0 stands for argument exact match & 1 stands for the number 1 Column of my data Range. Upon applying this formula Excel will look for the reference value of G6 Cell from E6:E15 Cells & return the exact value from Column 1 of my selected data Range.
  • This will give you output in column H for corresponding data in Column G.

  • Now as I have changed my 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.

Read More: Add Text and Formula in the Same Cell in Excel (4 Examples)


Similar Readings:


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.

excel find and replace in column

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

excel find and replace in column

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

excel find and replace in column

Read More: How to Replace Text in Excel Formula (7 Easy Ways)


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.

Related Content: How to Replace Special Characters in Excel (6 Ways)


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.
Note: However when you want to change the  asterisks or question symbols in your Excel worksheet, you need to use the tilde character (~) prior to those symbols. For instance If you want find Cells having asterisks, you have to type ~* in the Find what box. For finding Cells that contain question symbol, use ~? in the Find what box.

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


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

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo