How to replace multiple text in Excel using formula
Find and Replace Multiple Values at OnceUnderstand simple ways and formulas to substitute or swap multiple characters replacing other strings.When you have big data in a Workbook o
Find and Replace Multiple Values at Once
Understand simple ways and formulas to substitute or swap multiple characters replacing other strings.
When you have big data in a Workbook or Worksheet, and you observe that there are some mistakes, and the user wishes to remove all values at once which are wrong fields. To do this, we use the Substitute function and Find and Replace tool in Excel.
Additionally, in this article, you are going to learn how to use Find and Replace certain values, cell references, characters, Format, etc in Excel.
Key Topics which will you cover here are:
Table of Contents
About Find and Replace
Search and Replace Multiple Text
Use Wildcard to Replace
Replace Cell Reference in a Formula
Find and Change Formula
Replace or Find cells with a Format
Find and Substitute BreaksExample File
About Find and Replace
Find: To search any word, wildcard, symbol, formulas in data not only the active sheet but also in other sheets. The shortcut to finding in Excel is Ctrl+F
Replace: To substitute any values, symbols, formulas, etc. in the data of the worksheet which is found. The shortcut to replace in Excel is Ctrl+H
Find and Replace Dialog box with options.
As you click the Options button, Excel provides many options, in case you require more than simply find and replace, check settings: (Image Below)
- Match Case: If we click Match Case, the text which you type in Find must match exactly i.e. case sensitive.
- Match Entire Cell Contents: If this is selected, a match occurs if the cell contains only the characters that have an exact match and nothing else. For searching wildcard characters, an exact match is not required.
- Look In: It has three options: Values, Formulas, and Comments. The Formulas option only observes at the text that makes up the formula or the contents of the cell if there is no formula. The Values option looks at the cell value and the results, not the text, of the formula.
- Format: If there is a need to change the format (color) click the cell, Range to replace another Format. This drop-down has three options Format from the cell, Format, and clears the Format.
- Sheet: This has two options Find and Replace in a sheet or Workbook.
- Replace, Replace All, Find Next, Find All: If you want to replace only one cell which you select, Click on Replace, and if you want to change all similar values click on Replace All. The same is for Find Next and Find All, to find one value then another value press Find next, and to show all values together click Find All.
Where is Find and Replace in Excel?
- Open an Excel workbook.
- Under the Home tab
- In editing, group click on Find and Select
- Select Find and Replace option
- A dialogue box appears
- Enter your Query
Find Texts & Replace with another
To find and replace multiple text strings or values at once in Excel we can use the Excel SUBSTITUTE function that would swap multiple occurrences of existing characters from a given table or list with other numbers.
In this example, we want to find MidWest on a table and replace it with another string West.
=SUBSTITUTE(B3:E22,MidWest,West)
Using Find and Replace tool: When you want to remove and replace text with an exact name in a worksheet. Like here by mistake you have written a wrong Name or SalesRep, so to change, we will find Smith and replace it with Vinnie.
Search and Replace Name in a Column.
Replace Multiple occurrences of Texts
- Under the Editing group in Excel, click on Find and Select.
- Chose option Replace
- Find what: text (Smith)
- Click on Find All
- It shows the number of occurrences
- Then click on Replace
- Replace with: text (Vinnie) to change
- Click OK
Note- Same way you can Find and Replace any number in Excel.
Its not necessary we use find only to replace, sometimes we use to remove certain things in the spreadsheet.
Use Wildcard character To Replace
Sometimes we made spelling mistakes between two characters to make it correct quickly in a range use wildcard.
Here we have written Switzerland to replace one character we use ? (wildcard). Learn more about How to use Wildcards in Excel?
To change one letter in a Text.
Add & Remove ? character
- Under the Editing group, click on Find and Select
- Chose option Replace
- In Find what: type (z? r)
- Click on Find All
- It shows all occurrences
- Then click on Replace
- Replace with: type (zer) to change
- Click OK
Note: Like here you have changed only one character with ? If you have to change multiple characters use * in Excel.
Replace Cell Reference in a Formula
To change the entire cells used in the formula, Suppose that you have selected the wrong reference in a Formula. if we want the total of Achieve (D3:D22) but mistakenly you select the sum of Goal (C3:C22) to change it at once.
To Substitute cell Reference in a Formula.
Replace many cell reference at once
- Open Sheet to Change Cell Reference & Formula
- Under the Editing group, click on Find and Select
- Click the Formula cell
- Chose option Replace
- In Find what: type cell Reference (C3:C22).
- Then click on Replace
- Replace with: type cell Reference (D3:D22) to change.
- Click OK
Note: Do not forget to select Cell or Range in which you type your Formula before Find and Replace.
Find and Change Formula
In case you want to change part of the formula but cell reference remains the same. Like here we have the addition of Achieve and for this, we will change the formula to average.
To Replace Formula in a Cell
To Edit many Formula at once
- Open sheet Change Cell Reference & Formula
- Under the Editing group, click on Find and Select
- Select the cell to find
- Chose option Replace
- In Find what: type function =SUM()
- Then click on Replace
- In Replace with: type function =AVERAGE() to change
- Click OK
Note: Go through examples based on the condition of SUM and AVERAGE function.
Find and Substitute Breaks
Sometimes you have data in a column add with break maybe you have copied from another file with a Break or Wrap and looking to substitute it with space so that all values show in a cell we change to break with space.
Remove Breaks in cells
To Remove Break in cells
- Under the Editing group, click on Find and Select.
- Select the cell to find.
- Chose option Replace
- In Find what: type ctrl+j
- Then click on Replace
- Replace with: leave one space.
- Click OK
- Please review page how breaks applied in Print.
- To apply Breaks in a cell click on Alt+Enter
Replace or Find cells with a Format
To change the preview of format i.e. color of the background cell, we use Find and Replace in Excel. So, here the user highlights all Quantity which is less than 20 with Green color but to add a new color to change the Preview in the cells.
Replace colour Format in Excel.
Find and Replace Format
- Open Worksheet format
- Under the Editing group, click on Find and Select
- Select the Range
- Chose option Replace
- Click on Options
- In Find what: Click on a format select the color
- Then click on Replace.
- In Replace with: Click on format choose a color to replace.
- Click OK
Remember: Clear the format when work is done.
Please check how the format is done in Conditional Formatting detailed for coloring or specific format to Excel cells.
Note: Learn Count Cells with Find and Replace without VBA.
report this ad
SearchSearch
Latest Blog
Find value with INDEX & MATCH function in Excel
Finding and Managing Formula Errors in Excel
Print Excel Sheet Step by Step Guide
Average Based on Condition with 6 Examples
report this ad