How To Find And Replace Numbers In Excel
In this tutorial, you lot will learn how to utilize Find and Replace in Excel to search for specific information in a worksheet or workbook, and what you lot tin do with those cells afterwards finding them. We will also explore the advanced features of Excel search such as wildcards, finding cells with formulas or specific formatting, find and supercede in all open workbooks and more than.
When working with big spreadsheets in Excel, information technology'due south crucial to be able to apace discover the information you want at whatsoever particular moment. Scanning through hundreds of rows and columns is certainly not the mode to go, then permit's have a closer look at what the Excel Find and Replace functionality has to offering.
How to utilise Find in Excel
Below you will notice an overview of the Excel Discover capabilities besides as the detailed steps on how to use this characteristic in Microsoft Excel 365, 2019, 2016, 2013, 2010 and older versions.
Find value in a range, worksheet or workbook
The post-obit guidelines tell y'all how to find specific characters, text, numbers or dates in a range of cells, worksheet or entire workbook.
- To brainstorm with, select the range of cells to expect in. To search across the entire worksheet, click any cell on the agile canvas.
- Open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut. Alternatively, go to the Home tab > Editing group and click Notice & Select > Find…
- In the Find what box, type the characters (text or number) you are looking for and click either Find All or Notice Next.
When you click Detect Next, Excel selects the kickoff occurrence of the search value on the sail, the 2nd click selects the 2d occurrence, and so on.
When you click Find All, Excel opens a list of all the occurrences, and you tin click any item in the list to navigate to the corresponding cell.
Excel Detect - additional options
To fine-tune your search, click Options in the right-paw corner of the Excel Find & Supercede dialog, and and so do whatsoever of the following:
- To search for the specified value in the electric current worksheet or entire workbook, select Sheet or Workbook in the Within.
- To search from the active cell from left to right (row-by-row), select By Rows in the Search To search from top to bottom (column-past-cavalcade), select By Columns.
- To search among certain data blazon, select Formulas, Values, or Comments in the Look in.
- For a case-sensitive search, check the Match case check.
- To search for cells that contain only the characters y'all've entered in the Find what field, select the Friction match unabridged cell contents.
Tip. If you want to find a given value in a range, column or row, select that range, cavalcade(s) or row(s) earlier opening Find and Replace in Excel. For example, to limit your search to a specific column, select that cavalcade get-go, and and so open up the Notice and Replace dialog.
Find cells with specific format in Excel
To discover cells with certain formatting, press the Ctrl + F shortcut to open up the Find and Supervene upon dialog, click Options, then click the Format… button in the upper right corner, and define your selections in Excel Discover Format dialog box.
If you desire to find cells that match a format of some other prison cell on your worksheet, delete whatsoever criteria in the Find what box, click the arrow next to Format, select Choose Format From Cell, and click the cell with the desired formatting.
Notation. Microsoft Excel saves the formatting options that you specify. If you search for some other data on a worksheet, and Excel fails to find the values that you know are in that location, clear the formatting options from the previous search. To exercise this, open up the Find and Replace dialog, click the Options button on the Discover tab, so click the pointer adjacent to Format.. and select Clear Find Format.
Find cells with formulas in Excel
With Excel's Discover and Replace, you can only search in formulas for a given value, every bit explained in additional options of Excel Discover. To find cells that contain formulas, employ the Go to Special feature.
- Select the range of cells where you desire to find formulas, or click any prison cell on the current sheet to search across the entire worksheet.
- Click the arrow next to Find & Select, and then click Go To Special. Alternatively, yous tin press F5 to open up the Become To dialog and click the Special… button in the lower left corner.
- In the Go To Special dialog box, select Formulas, then bank check the boxes respective to the formula results you lot want to observe, and click OK:
- Numbers - discover formulas that return numeric values, including dates.
- Text - search for formulas that return text values.
- Logicals - find formulas that return Boolean values of TRUE and Faux.
- Errors - find cells with formulas that result in errors such as #North/A, #Proper name?, #REF!, #VALUE!, #DIV/0!, #Zilch!, and #NUM!.
If Microsoft Excel finds any cells that encounter your criteria, those cells are highlighted, otherwise a message volition be displayed that no such cells have been establish.
Tip. To quickly find all cells with formulas, regardless of the formula result, click Find & Select > Formulas.
How to select and highlight all found entries on a sheet
To select all occurrences of a given value on a worksheet, open up the Excel Observe and Supersede dialog, type the search term in the Discover What box and click Find All.
Excel will display a listing of establish entities, and you lot click on any occurrence in the list (or only click anywhere inside the results expanse to move the focus there), and press the Ctrl + A shortcut. This volition select all found occurrences both on the Find and Supervene upon dialog and on the sheet.
Once the cells are selected, you can highlight them by changing the fill up color.
How to apply Replace in Excel
Below you will notice the step-by-pace guidelines on how to utilize Excel Replace to change ane value to some other in a selected range of cells, entire worksheet or workbook.
Supervene upon one value with another
To supersede sure characters, text or numbers in an Excel sheet, make use of the Replace tab of the Excel Detect & Replace dialog. The detailed steps follow beneath.
- Select the range of cells where you want to replace text or numbers. To supervene upon grapheme(s) across the entire worksheet, click any prison cell on the agile canvas.
- Printing the Ctrl + H shortcut to open the Replace tab of the Excel Detect and Replace dialog.
Alternatively, go to the Home tab > Editing grouping and click Find & Select > Supervene upon…
If y'all've simply used the Excel Observe feature, then but switch to the Replace tab.
- In the Find what box type the value to search for, and in the Supersede with box blazon the value to supplant with.
- Finally, click either Replace to supersede the found occurrences one by 1, or Replace All to swap all the entries in one barbarous swoop.
Tip. If something has gone wrong and you got the outcome dissimilar from what you'd expected, click the Undo push button or press Ctrl + Z to restore the original values.
For additional Excel Replace features, click the Options button in the right-hand corner of the Replace tab. They are essentially the same as the Excel Find options we discussed a moment ago.
Replace text or number with nothing
To replace all occurrences of a specific value with nothing, type the characters to search for in the Discover what box, leave the Replace with box blank, and click the Supersede All push button.
How to find or replace a line interruption in Excel
To replace a line break with a space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII command code for character 10 (line pause, or line feed).
After pressing Ctrl + J, at commencement sight the Find what box will look empty, only upon a closer look y'all will find a tiny flickering dot similar in the screenshot below. Enter the replacement character in the Replace with box, due east.thousand. a space character, and click Replace All.
To supersede some grapheme with a line break, do the opposite - enter the current character in the Observe what box, and the line break (Ctrl + J) in Replace with.
How to change prison cell formatting on the sheet
In the kickoff function of this tutorial, we discussed how you can find cells with specific formatting using the Excel Find dialog. Excel Replace allows you to take a pace farther and change the formatting of all cells on the sheet or in the entire workbook.
- Open the Replace tab of Excel'due south Find and Supercede dialog, and click the Options
- Next to the Detect what box, click the arrow of the Format button, select Choose Format From Cell, and click on any cell with the format you want to change.
- Next to the Supplant with box, either click the Format… push and set the new format using the Excel Supervene upon Format dialog box; or click the pointer of the Format button, select Choose Format From Jail cell and click on any cell with the desired format.
- If you want to replace the formatting on the unabridged workbook, select Workbook in the Within box. If y'all want to supersede formatting on the active sheet merely, leave the default selection (Sheet).
- Finally, click the Replace All button and verify the result.
Excel Find and Supersede with wildcards
The use of wildcard characters in your search criteria can automate many detect and replace tasks in Excel:
- Utilize the asterisk (*) to detect any string of characters. For example, sm* finds "smile" and "olfactory property".
- Use the question mark (?) to detect any single character. For instance, gr?y finds "Gray" and "Grey".
For example, to get a list of names that begin with "advert", use "ad*" for the search criteria. As well, delight keep in heed that with the default options, Excel will search for the criteria anywhere in a cell. In our case, it would return all the cells that take "advert" in whatever position. To prevent this from happening, click the Options push, and check the Match entire jail cell contents box. This will force Excel to return only the values beginning with "ad" every bit shown in the below screenshot.
How to find and replace wildcard characters in Excel
If y'all need to find bodily asterisks or question marks in your Excel worksheet, type the tilde character (~) before them. For example, to find cells that contain asterisks, you lot would type ~* in the Find what box. To find cells that comprise question marks, apply ~? equally your search criteria.
This is how you can replace all questions marks (?) on a worksheet with some other value (number 1 in this example):
Equally you see, Excel successfully finds and replaces wildcards both in text and numeric values.
Tip. To find tilde characters on the sheet, type a double tilde (~~) in the Find what box.
Shortcuts for detect and supplant in Excel
If you have been closely following the previous sections of this tutorial, y'all might have noticed that Excel provides 2 dissimilar ways to interact with Discover and Replace commands - by clicking the ribbon buttons and past using the keyboard shortcuts.
Below at that place is a quick summary of what you've already learned and a couple more shortcuts that may save you lot a few more seconds.
- Ctrl+F - Excel Find shortcut that opens the Detect tab of the Detect & Supercede
- Ctrl+H - Excel Supplant shortcut that opens the Supersede tab of the Find & Supersede
- Ctrl+Shift+F4 - find the previous occurrence of the search value.
- Shift+F4 - observe the side by side occurrence of the search value.
- Ctrl+J - find or supervene upon a line suspension.
Search and supercede in all open up workbooks
As you accept just run into, Excel's Find and Supplant provides a lot of useful options. Even so, it can search but in i workbook at a time. To find and replace in all open workbooks, you can use the Advanced Find and Supplant add-in by Ablebits.
The following Avant-garde Detect and Supersede features make search in Excel fifty-fifty more powerful:
- Find and Replace in all open up workbooks or selected workbooks & worksheets.
- Simultaneous search in values, formulas, hyperlinks and comments.
- Exporting search results to a new workbook in a click.
To run the Advanced Find and Supplant add-in, click on its icon on the Excel ribbon, which resides on the Ablebits Utilities tab > Search group. Alternatively, you can press Ctrl + Alt + F, or even configure it to open by the familiar Ctrl + F shortcut.
The Advanced Find and Supercede pane will open, and yous do the post-obit:
- Type the characters (text or number) to search for in the Discover what
- Select in which workbooks and worksheets y'all want to search. By default, all sheets in all open workbooks are selected.
- Cull what data type(due south) to look in: values, formulas, comments, or hyperlinks. By default, all information types are selected.
Additionally, you take the following options:
- Select the Match case selection to look for case-sensitive data.
- Select the Entire cell check box to search for verbal and complete lucifer, i.eastward. find cells that contain only the characters you've typed in the Find what
Click the Detect All button, and you will run into a listing of found entries on the Search results tab. And now, you lot tin can replace all or selected occurrences with some other value, or export the found cells, rows or columns to a new workbook.
If you are willing to try the Advanced Find and Replace on your Excel sheets, y'all are welcome to download a fully functional trial version.
I thank you for reading and promise to see you on our weblog next week. In our text tutorial, we volition dwell on Excel SEARCH, Find and Replace functions, so please keep watching this infinite.
You may also be interested in
Source: https://www.ablebits.com/office-addins-blog/2015/09/29/using-excel-find-replace/
Posted by: pittmanheinink1945.blogspot.com
0 Response to "How To Find And Replace Numbers In Excel"
Post a Comment