Find and Replace in Excel – Shiksha Online
Find & Replace in Excel are crucial and frequently used functions. Learn how to use these functions using both formula and advanced options.
The Find & Replace function in Excel allows to search for a desirable text or numeric string or character in a workbook. Both features appear together, but they perform separately. The Find function allows you to locate a particular number or text string, while the Replace function helps you replace a particular number or text string with something else.
Let’s take a look at these two functions separately.
Content
- FIND function
- REPLACE function
- Advanced Find and Replace
- Examples of Advanced Find and Replace
- Advanced Find & Replace Options
FIND function
The Find function in Excel allows you to search for a desirable text or numeric string or character in a workbook. The FIND function in Excel is a well-known formula within the search category that allows you to find the position of the searched word within a text string. This function is used in much the same way as the MATCH and INDEX functions.
With the FIND function in Excel, you can –
- Search by rows and columns
- Search within worksheets
- Search in an entire workbook
- Search within comments or values
Category: Search and reference functions
Use: The FIND function in Excel searches for a word, or a letter, within a text string. If the function finds the searched word, it will return the position occupied by the first letter within the original text string.
Read MS Excel Tutorials
FIND Syntax:
=FIND (find_text, within_text, [start_num])
Keyboard Shortcut for FIND Function
CTRL + F
Explanation of the FIND function in Excel
This function has 3 arguments:
find_text: This is a required argument. It is the text you want to find.
within_text: This is a required argument. It is the text string that contains the text you want to find.
start_num: This is an optional argument. It specifies the character from which the search begins. The first character of within_text is the number character 1. If you omit start_num, it is assumed to be 1.
Observations
- The FIND and FINDB functions are case-sensitive and do not allow the use of wildcards. If you don’t want to perform a case-sensitive search or use wildcards, use SEARCH.
- If find_text is “” (empty text), FIND matches the first character of the search string (that is, the start_num character or 1).
- Search_text cannot contain wildcard characters.
Read Later
How to Apply the FIND Formula in Excel
We have the below data where we will apply the Find function. In cell C2, we will apply the Find formula to locate the substrings in Column B from strings in Column A.
Here
find_text=B2
within_text=A2
When you press enter, it will show you the instances of the appearance of F in A2.
We can see that F is located at the first position of the string in A2. Now we will quickly fill the remaining cells.
C3=PositIon of ‘a’ in B3
C4=PositIon of ‘and’ in B4
C5=No instance of ‘q’ in the string; hence the Find function will return the error #VALUE!
Must Read – What is MS Excel?
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
REPLACE function
The Replace function allows you to modify a text or number according to the parameters you use. You can –
- Include wildcard characters such as question mark (?), tilde (~), asterisks (*), or numbers in the search terms
- Replace by rows and columns
- Replace within worksheets
- Replace in an entire workbook
- Replace within comments or values
Category: Text Functions
Use: The REPLACE function in Excel is used to replace part of a text string, based on the number of characters you specify, with a different text string.
REPLACE Syntax
=REPLACE (original_text, start_num, num_chars, new_text)
Keyboard Shortcut for REPLACE Function
CTRL + H
Explanation of the REPLACE function in Excel
This function has 4 arguments:
old_text: This is a required argument. It is the text in which you want to replace some characters.
start_num: It is a mandatory argument. It is the character’s position within original_text you want to replace with new_text.
num_chars: It is a mandatory argument. It is the number of characters of original_text you want to replace with new_text.
new_text: This is a required argument. It is the text that will replace the original_text characters.
How to Apply the Replace Formula in Excel
In this dataset, we want to replace a part of the text string in cell A2. We will apply the formula –
=REPLACE(A2,5,3,”nor”)
Where
A2=The cell with old text
5=Position of the character that needs to be replaced
3=Number of characters to be replaced
nor=New text
Press Enter.
- You can also use UPPER case replacement.
- Replace with digits.
- Replace some other position within the text string.
Advanced Find and Replace
Use search options in Excel to use the advanced find and replace functions in the workbook.
- Click Find & Select
Home ⇒ Find & Select
- Select Find or Replace
- Click the Options button
You will see additional options appearing. You can select the required, advanced Find and Replace options and perform the task of finding or replacing the content as you normally would.
Examples of Advanced Find and Replace
Let’s take a look at examples of Find and Replace using the Advanced options without using any formulas.
Find Values
In the below dataset, we want to find the entries on a given criterion. We are selecting finding cells with the country name Thailand.
Go to Find & Select and choose Find. Here we have to find cells with the entry – Thailand, just use the text string Thailand.
Click on Find All, you will get all the instances with Thailand.
Here, you can see that Excel found 726 cells that contain the text string Thailand. In this search result, the references are –
Book – Spreadsheet name
Sheet – Tab name
Cell – Cell name
Value – Value that we wanted to find
Replace Values
We will use the same dataset to replace the Department. For example – we will replace the Department Clothing with Lifestyle.
Go to Find & Select and choose Replace. Here we have to find cells marked Clothing, just use –
Find what – Clothing
Replace with – Lifestyle
Click on Replace All. Lifestyle will replace all the instances with the text string Clothing.
Here, you can see that Excel found 726 cells that contain the text string Thailand. In this search result, the references are –
Book – Spreadsheet name
Sheet – Tab name
Cell – Cell name
Value – Value that we wanted to find
Advanced Find & Replace Options
| Option | Description |
| Format | We can set the format as search criteria. Allows selecting a specific format that we want to search for, including character and paragraph formats, special characters, alignment, font, border or fill, document elements, styles, etc. |
| Within | Allows selecting the search in the sheet or in the entire book. |
| Search | Allows customizing the way Excel will perform the search. We can search by rows (left to right, then top to bottom) or columns (top to bottom, then left to right). |
| Look in | Allows narrowing your search based on the type of cell content, such as formulas, values, notes, or comments. |
| Match case | Excel will distinguish between upper and lower case to perform the search. By default, this option is not activated. |
| Match entire cell contents | Allows searching cells that match the contents in the text box entirely. This option won’t include any parts of strings. |
We hope this article helped you in understanding the concept of Find & Replace in Excel and how to apply them using real-life datasets. Keep learning and exploring Microsoft Excel.
Top Trending Articles in MS Excel:
Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel
Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio
Top Picks & New Arrivals
