{"id":445,"date":"2017-05-30T09:41:12","date_gmt":"2017-05-30T14:41:12","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=445"},"modified":"2017-06-27T10:20:05","modified_gmt":"2017-06-27T15:20:05","slug":"find-replace-in-excel","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/find-replace-in-excel\/","title":{"rendered":"Find &#038; Replace in Excel"},"content":{"rendered":"<p>The Find And Replace feature in Excel is a very useful, but often overlooked tool in Excel. Obviously it can help you find information in your worksheet. But it can also help you quickly update information scattered across a worksheet by making replacements in the text, numbers, formulas, or even formatting on your spreadsheet.\u00a0 The easiest way to use this pair of related features is to type <em>Ctrl+F<\/em> for Find or <em>Ctrl+H<\/em> for Replace (I can&#8217;t explain why H). Or if you prefer to use the mouse the <em>Find &amp; Select<\/em> button is at the far right end of the <em>Home<\/em> ribbon.<\/p>\n<p>I&#8217;m going to focus on &#8220;Replace&#8221; instead of &#8220;Find&#8221; because the two work the same way with the obvious exception that Find only locates things and does not replace them with something else.<\/p>\n<p>At its most basic, you enter the text to be located (the text you want to replace) in the <em>Find What<\/em> field and the text you want to use in its place in the <em>Replace With<\/em> field (or leave <em>Replace With<\/em> blank to simply remove text you searched for). If you want to replace every occurrence, click the <em>Replace All<\/em> button. If you want to review each instance match before replacing it, click the <em>Find Next<\/em> button. Then click <em>Replace<\/em> to or click F<em>ind Next<\/em> again to move on without replacing that occurrence.<\/p>\n<p>By default, the entire tab of the workbook will be searched. To limit your search to a specific area, simply highlight those cells first.<\/p>\n<p>Excel looks at your formulas too, not just at text or numbers. So if you have several formulas that reference cell A5 and now you want them to reference cell C10, just enter A5 in <em>Find What<\/em> and C10 in <em>Replace With<\/em> and you can easily update all of you formulas at once.<\/p>\n<p>Instead of looking for an exact string of text, you can also use wildcards when searching. Use a ? as a wildcard to a single character or a * for multiple characters. For example, searching for \u201c<em>c?t<\/em>\u201d would find both cat and cut. But a search for <em>\u201cc*t\u201d<\/em> would find court as well as cat and cut.<\/p>\n<p>When you click the <em>Options<\/em> button in the <em>Replace<\/em> window you will see several ways you can refine your search.<\/p>\n<ul>\n<li><em>Format<\/em> lets you add formatting requirements to either your \u201cFind What\u201d or \u201cReplace With\u201d choices. You can even find or replace just formatting without specifying any text.<\/li>\n<li><em>Within<\/em> tells Excel to search just the current worksheet (tab) or the entire workbook. If you set it to \u201cSheet\u201d you can limit it further by highlighting a group of cells. Then it will only search the selected cells.<\/li>\n<li><em>Search<\/em> controls the order in which Excel looks for your text. Choices are by columns or by rows.<\/li>\n<li><em>Look In<\/em> has choices for Formulas, Values, or Comments on the <em>Find<\/em> tab, but <em>Formulas<\/em> is the only choice on the <em>Replace<\/em> tab.<\/li>\n<li><em>Match Case<\/em> turns on the option to match capitalization, not just letters.<\/li>\n<li><em>Match entire cell contents<\/em> changes your search from replacing a match anywhere within a cell to replacing only when the cell\u2019s entire contents matches your search text.<\/li>\n<\/ul>\n<p>Next time you need to make changes in a spreadsheet, think about using Find And Replace before you waste time manually searching through your spreadsheet and typing each update.<\/p>\n<p><iframe loading=\"lazy\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/5y6TIlQLBq0?feature=oembed\" frameborder=\"0\" allowfullscreen><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>This post is an update of a tip originally publish in March 2012.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel&#8217;s Find and Replace feature lets you quickly update information anywhere in your spreadsheet with just a few clicks.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/445"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=445"}],"version-history":[{"count":7,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/445\/revisions"}],"predecessor-version":[{"id":1085,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/445\/revisions\/1085"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}