How To Use Find And Replace In Excel

Watch Video – Useful Examples of Using Find & Replace in Excel

Terminal month, ane of my colleagues got a data set in Excel, and he was banging his caput to clean it. Since I was the only 1 in the office at that wee hour, he asked me if I could help. I used a uncomplicated technique using Detect and Replace in Excel, and his data was all clean and polished. He thanked me, packed up, and left office.

He thanked me, packed upward, and left office.

Excel Find and Supersede characteristic is super powerful if you know how to all-time use it.

Using Detect and REPLACE in Excel (4 Examples)

Observe and Supercede in Excel can salvage a lot of time, and that is what matters about these days.

In this blog, I will share four amazing tips that I have shared with hundreds of my colleagues in my office. The response is e'er the aforementioned – "I wish I knew this earlier. It could have saved me and so much of hard labor".

#1 To Change Cell References Using Excel Find and Replace

Sometimes when y'all work with a lot of formulas, there is a need to change a prison cell reference in all the formulas. Information technology could have you a lot of time if y'all manually change it in every cell that has a formula.

Here is where Excel Find and Supercede comes in handy. Information technology tin easily notice a cell reference in all the formulas in the worksheet (or in the selected cells) and replace it with another cell reference.

For example, suppose you have a huge dataset with formula in that uses $A$1 as one of the cell references (as shown below). If you need to modify $A$1 with $B$1, you can practice that using Find and Replace in Excel.

Here are the steps to practice this:

1. Select the cells that take the formula in which you lot desire to replace the reference. If you lot want to replace in the entire worksheet, select the entire worksheet.
2. Go to Home –> Detect and Select –> Replace (Keyboard Shortcut – Control + H).
3. In the Observe and Supercede dialogue box, use the following details:
   • Find what: $A$i (the prison cell reference you want to change).
   • Replace with: $B$1 (the new jail cell reference).
4. Click on Supersede All.

This would instantly update all the formulas with the new cell reference.

Note that this would change all the instances of that reference. For example, if yous accept the reference $A$i two times in a formula, both the instances would be replaced past $B$1.

#2 To Find and Supercede Formatting in Excel

This is a cool feature when y'all desire to replace existing formatting with some other formatting. For example, yous may have cells with an orangish groundwork color and you want to modify all these cell's background colour to red. Instead of manually doing this, utilize Discover and Replace to do this all at once.

Here are the steps to exercise this:

1. Select the cells for which yous want to detect and replace the formatting. If you want to observe and supplant a specific format in the entire worksheet, select the unabridged worksheet.
2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).
3. Click on the Options push. This will expand the dialogue box and bear witness you lot more than options.
4. Click on the Find what Format button. Information technology will bear witness a drop-downwardly with two options – Format and Cull Format from Prison cell.
5. Now you demand to specify the format that yous desire instead of the ane selected in the previous stride. Click on the Supersede with Format push button. Information technology will show a drib-down with two options – Format and Choose Format from Cell.
6. Click on the Supplant All button.

Yous tin apply this technique to replace a lot of things in formatting. It tin pick up and supervene upon formats such as background color, borders, font type/size/color, and even merged cells.

#3 To Add or Remove Line Break

What do you exercise when yous accept to go to a new line in an Excel jail cell.

You lot press Alt + Enter.

And what exercise you do when you lot want to revert this?

You delete information technology manually.. isn't it?

Imagine you have hundreds of line breaks that you want to delete. removing line breaks manually can take a lot of fourth dimension.

Here is the good news, you don't need to do this manually. Excel Find and Replace has a cool trick up its sleeves that volition brand it happen in a snap.

Here are the steps to remove all the line breaks at once:

1. Select the information from which yous want to remove the line breaks.
2. Go to Dwelling house –> Detect and Select –> Supercede (Keyboard Shortcut – Control + H).
3. In the Discover and Replace Dialogue Box:
   • Find What: Printing Control + J (you may not see anything except for a blinking dot).
   • Replace With: Space bar graphic symbol (hit space bar once).
4. Click on Supercede All.

And Woosh! It would magically remove all the line breaks from your worksheet.

#4 To Remove Text Using Wildcard Characters

This one saved me hours. I got a list equally shown below, and I had to remove the text between parenthesis.

If yous accept a huge data-set, removing the parenthesis and the text between it can take you lot hours. But Find and Replace in Excel tin practice this in less than ten seconds.

1. Select the data
2. Become to Home –> Find and Select –> Replace (Keyboard Shortcut –Control + H)
3. In the Detect and Replace Dialogue Box:
   • Find What: (*)
     Note I accept used an asterisk, which is a wildcard character that represents any number of characters.
   • Supercede With: Leave this Blank.

I promise you find these tips helpful. If there are any other tricks that helped you save time, practise share it with us!!

You May Also Like the Following Excel Tips & Tutorials:

• 24 Excel Tricks to Brand You lot Sail through Solar day-to-twenty-four hour period work.
• 10 Super Neat Means to Make clean Information in Excel Spreadsheets.
• 10 Excel Data Entry Tips Yous Can't Afford to Miss.
• Suffering from Slow Excel Spreadsheets? Try these 10 Speed-upward Tips.
• How to Filter Cells with Bold Font Formatting in Excel.
• How to Transpose Data in Excel.

Source: https://trumpexcel.com/find-and-replace-in-excel/

Posted by: jonesbuzzlieve.blogspot.com

Share this post