Asif Khan Pranto
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.
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.
- Now all the Chips have been Replaced with Crackers.
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.
- 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.
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:
- 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 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:
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)
- How to Find and Replace Multiple Words at Once in Excel (7 Methods)
- Excel VBA: How to Find and Replace Text in Word Document
- Excel VBA to Find and Replace Text in a Column (2 Examples)
- How to Substitute Multiple Characters in Excel (6 Ways)
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.
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.
- 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
- Data clean-up techniques in Excel: Adding text to cells
- Find And Replace Multiple Values in Excel (6 Quick Methods)
- How to Use the Substitute Function in Excel VBA (3 Examples)
- How to Find and Replace Asterisk (*) Character in Excel
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
