A time comes when you need to change or replace data from your Excel sheet. Whenever you tend to update a worksheet, you have to make apply different modifications to make the sheet presentable. And when you have a huge dataset, it would be entirely difficult for you to do it manually because it is a time-consuming process. Finding and replacing specific values from data would not be an easy thing at all.
For this reason, Excel has Find and Replace features that help in searching data from the workbooks. You may have to find a certain number or a text string. Not only numbers or text, but you may also sometimes need to replace wildcard characters including tildes, question marks, or asterisks. You can find and replace words by rows and columns, within comments or values, and within a sheet or workbook.
Find Text
Using the Find feature, you can easily locate specific words as well as values from a sheet. Follow the given steps for complete understanding.
- Choose the Find & Select option from the Home tab.
- Click on the Find option.
- Press CTRL + F keys from the keyboard.
- The Find and Replace dialog box will appear.
- Enter the text that is needed to find in the Find what field.
- Click the Find Next option.
- Clicking the Find Next option will jump the search from one occurrence to the next word, or value entered in the sheet.
- Choose the Find Next button once again.
- Choose to Find All to find the cell location of each instance.
- Press the Close button to finish the process.
How to Replace Words in Excel with Replace Feature
Sometimes you need to replace a name, value, number, or any particular string from the worksheet. The Find and Replace feature is used in this case to make quick moves. By using the Replace tab from the Excel Find & Replace menu, you can replace particular characters, texts, or numbers from the Excel sheet. Follow the steps given below for further understanding:
- Choose a cell range in which you need to replace text or numbers. Click on the cell to replace characters across the entire worksheet.
- Open the Home tab and choose the Find & Select option. Click on the Replace button. Or else you can even press the CTRL + H keys from the keyboard to open the Replace tab.
- Switch to the Replace tab if you’ve used the Excel Find feature. Enter the value you need to search in the Find what field box. Put the value to replace in the Replace with field box.
- Choose to Replace option to replace the found occurrences one by one or you can even choose to Replace All option to swap all the values in one step.
- Press the Close button to exit when the process is finished. The Find and Replace dialog box will be closed.
Shortcuts for Find and Replace in Excel
The above-mentioned methods mainly use the Find and Replace feature of Excel. The Find and Replace option is placed in a ribbon that can be approached easily. On the other hand, if you don’t want to opt for the above methods, you can use the shortcut keys to replace words in Excel.
- Press CTRL + F keys to open the Find tab from the Find and Replace.
- Press CTRL + H keys to open the Replace shortcut from the Find and Replace option.
- Press the CTRL + SHIFT + F4 keys from the keyboard to locate the earlier presence of the search value.
- Press the SHIFT + F4 keys from the keyboard to identify the next presentation of the search value.
- Press CTRL + J keys to find and replace a line break.
Replace Data with Nothing
Sometimes you need to locate a particular value, text, or number to replace with nothing. For this, you can enter the characters or values in the Find what box to search and leave the Replace with box empty. Click on the Replace All button.
Final Thoughts
Finding and replacing values in an Excel sheet could be a real-time headache if Find & Replace function is not introduced. No one can find and replace a particular word, number, character, or any value manually. Excel has been a handy tool whenever you need to find, gather and, manage data analytically.
