[wp_ad_camp_2]
Everyone know that we can find and replace a text with find and replace option. But very few people know that you can use this option to an advance level.
Do you?
If not, then after reading this post youβll love this tool. In this post, you will learn some useful examples to use find and replace option to an advance level.
So let’s get started.
[wp_ad_camp_5]
First of all, you have to activate advanced options in find and replace. Click on the “Options” button to activate.
With advanced options, you can find values using different parameters. You can specify conditions like searching for specific formulas, cells with specific colors, bold texts, etc.
Here are some useful examples for searching some specific values:
- Value In Entire Workbook
- Cell Having a Specific Formula
- Values from Cell Comments
- Copy Format From A Cell To Find Another Cell
- Having Specific Format
Find Value In Entire Workbook
You can find a value from your entire workbook rather than from a single worksheet.
And, you can also replace that value with some other specific value.
- Step-1: To find a value in an entire workbook, select workbook from βWithinβ drop down option.
- Step-2: Enter search value in “Find what” to search & enter replace value in “Replace with” to replace.
Find Cell Having a Specific Formula
This is also a useful option to find a cell which has a specific formula and replace that formula with some other specific formula.
- Step-1: To find a cell with a specific formula, activate your find and replace option. And, select formula option from βLook inβ drop down.
- Step-2: Now, enter the formula in “Find what” to search for formula and enter the formula in “replace with” you want to replace.
Find Specific Value From Cell Comment
This one is my favorite. You can find the cell in which you have a cell comment with a specific value. This will help you in two ways, first, it will search for the cells which have the cell comment and then it will search a specific value in that comment.
- Step-1: To do this, activate your find and replace option. Select βCommentsβ from βLook Inβ dropdown.
- Step-2: Add the value in βFind Whatβ you want to search for.
- Step-3: Click find or find all.
Copy Format From A Cell To Find Another Cell
Here we have another useful option to find a cell on the basis of value and format of a specific cell.
And, after that, you can also change that value and format.
- Step-1: Click on the down arrow on the format button.
- Step-2: Select βChoose Format From Cellβ. You will get a selection tool to select the cell which you want to use as a base for your search.
You can also select a cell to take as a base for replacement.
Find Cell Having Specific Format
You can also use the custom format to search and replace cell values and formats.
- Step-1: Activate your find and replace option, Click on the format button.
- Step-2: You’ll get a format dialog box to select the format to find for. Select the desired format to search for.
Using this option you will able to make any type of search basis on the format.
You can search for:
- Cell Borders
- Font Colors
- Background Color
- Number Format
- And, much more…
Things to Remember
- There is no option to replace the value in cell comment. π
- While searching for a format, you can only search for the cells which exactly have the same format.
- And for text, you have the option to match case.
- You can also match exact cell content for your search.
Conclusion
Using find and replace this way can save your lot of time. This can give you an advanced way to search for the values. And the best part is you, you can replace those values.
I hope you like it. Do you have any unique idea to use this option? Please share your view in the comment box, I would love to hear from you.
Informative
Thank you for this email. I just want to clarify that if I set my autosave to 1min then will I see the auto recover at 1:25am and 1:24am or will there be an earlier option.
If not an earlier option then please can you elaborate more on One Drive and saving more versions to cloud.
In loved your Find and Replace techniques
Thanks Puneet,
It has been very informative and revealing.
These are great things to get the options tools for find and replace. I have one thing to ask you about how to change color on result of find option becasue I always found that the color is grey. Some times I prefer other color like pink or blue etc.
Very well done and extremely useful. Thank you.
Nice
Awesome!!
Thanks for your words
Nice tips. Well I had seen the advanced option but never knew how to use it. Thanks for the blog
Welcome Krupesh.
I hope you’ll continue to use it.
nice tips
I am so glad you liked it.