How to Find and Replace an Excel Column (6 Ways) - ExcelDemy (2023)

In this article we will learn aboutSearch in ExcelIsubstituteWColumnusing some simple methods. To make the work easier, we can use the so-calledCome acrossIsubstitutefeature to easily spot something similarProwess,Text,Type,Formetc. &substituteI.

Suppose we have a data set from the company's salesDeadline,Area,Seller,product category,Product&Total salesrespectively incolumns,si,do,Hey,m,eat&letters.

Download the workbook

6 easy ways to find and replace in an Excel column

1. Find and replace in a column using Find and Replace

In this method, I will show you how to do it.find and replaceanything insidecolumnsusingCome across&Dialog replacement.

Steps:

• I am usingCTRL keyboard shortcut+Hor go todom>version>Find and select > Replace.

• then insidefind thiswrite in the field what you want to find for the exchange & wexchangewrite in the box what you want to replace.
• In my dataset I want to replaceFrench frieszpretzels.
• To do this, type the following command as shown in the image below.

• If you likereplace allselect immediatelyreplace allOr if you wantsubstituteone by one just clicksubstitute.
• I chosereplace all.
• After clicking on itMSExcelwill show you adialog boxsaid the number of replacements.

• Now everythingFrench frieshave been replaced bypretzels.

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

2. Find and replace in a column for a specific format

In this method, we will learn how to do it.Come acrossIsubstituteWcolumnsfor specific formats. By reading this, we learn how to do it.substitutespecificFormwith differentForm.

Steps:

• open firstCome across&Dialog replacement.
• Then click to learn moreChoices.

• then to selectFormI am doingCome across, click Firstform frame& Sofallmenu will appear.
• offallchooseSelect Cell Format.

• Socollectorwill appear and you will select aThe cellcontainingFormI wantCome across.

• now choose anotherFormI wantsubstitute.
• clickform framebelow to choose.

• Now from the field select the desired oneFormwhat do you wantsubstitute.
• I want to change the data set fromaccounting formI am doingcurrency form
• Here I chosecurrency form.
• then pressOkay.

• now pressreplace all.

• After that, the dataset will be replaced bycurrency form.

Related Content: Conditionally Replace Cell Text in Excel (5 Easy Methods)

3. Find the type to replace in a Find and Replace column

Here I will show you how to do it.Come across&substituteONETypewColumn.

Steps:

• Suppose you didINDEXPARTY formulaWColumn H.
• The guy is.

• This will give you an output.pillar Xfor the relevant data atColumn C.

• Now that I've changed minei columnand his name changedProduct, the formula will produce no output in the filespine area.

• Now, to modify the formula accordingly, go toCome across&Dialog replacement.
• Then type the following commandfind this&exchangefield to modify the formula.
• then pressreplace all.

• Will beCome across&substitutedesired part of the formula and display the result.

Read more: Add text and formula to the same cell in Excel (4 examples)

Related reading:

• [Fixed!] Find and Replace in Excel 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 a Word document
• Excel VBA to find and replace text in a column (2 examples)
• How to Replace Multiple Characters in Excel (6 Ways)

4. Find and replace with anything in an Excel column

In this section we will see how to do it.Come acrossanything in aColumn&substitutethis with nothing

Steps:

• First choose oneColumnWhere you wantCome across&substituteYour information.
• I chosecomment columnmy data set.

• then openCome across&Dialog replacement.
• Here I want to deleteIt failedofcomment column. To do this, typeIt failedWfind a box& saveReplace with box.

• after selectionreplace allwill have the desired data set.

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

5. Find the newline and replace it

now we will see how to do itsubstituteONEline breakWI pay.

Steps:

• select firstColumndataset you haveLine breaks.
• In my data set, I choseColumn ChavingLine breaks.

• now openCome across&substitute dialog box.
• then writeCONTROL KEY+iin researchWhat a box. this is what it will look likebeep pointwhat is a symbolLine breaks.
• keepReplace with box.
• then clickreplace all.

• Now you will have the data set you want.

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

6. Use wildcards to find and replace in Excel

Here I will show you how to do it.Come acrossIsubstituteusingwild cardMode.

Steps:

• First choose oneColumnWhere you wantCome across&substitute.

• then openCome across&substitutedialoguebox.
• Now we will usewild cardprojectsCome across&substitute.
• We can use Excelwild cardmode for various options inclDiscoveryIExchangeManycharacters.This can be done usingestrella (*). For exampleof*can spot words"Abraham"I"Abram".
• We can findunique characterusingquestion mark (?).PiotrIPiotrboth are usedPiotr.
• In oursColumnwe want to findcellshavingproduct codeStarting with meONE&substitutea zExhausted.
• then I will writeONE*Wfind a boxand writeExhaustedWReplace with box.
• then pressreplace all.

• Now you will have the data set you want.

Read more:How to find and replace wildcard values ​​in Excel

exercise sheet

I have prepared a worksheet for you. Try it.

Application

By reading the article above, we found outSearch in Excel&substituteWI payusing various criteria. WithCome across&substitutethe feature really makes our job easier. I hope you had fun reading this article. If you want to ask something, leave a comment.

related posts

• Data cleaning techniques in Excel: adding text to cells
• Find and Replace Multiple Values ​​in Excel (6 Quick Methods)
• How to use the replace function in Excel VBA (3 examples)
• How to find and replace the asterisk (*) character in Excel
• Find and Replace Text in a Range with Excel VBA (Macro and User Format)