{"id":4918,"date":"2026-01-02T21:12:21","date_gmt":"2026-01-02T20:12:21","guid":{"rendered":"https:\/\/sandshine.eu\/?p=4918"},"modified":"2025-12-01T21:21:28","modified_gmt":"2025-12-01T20:21:28","slug":"substitute-function-in-excel-complete-tutorial-with-examples","status":"publish","type":"post","link":"https:\/\/sandshine.eu\/index.php\/2026\/01\/02\/substitute-function-in-excel-complete-tutorial-with-examples\/","title":{"rendered":"SUBSTITUTE Function in Excel: Complete Tutorial with Examples"},"content":{"rendered":"\n<p>Need to replace text within a cell? SUBSTITUTE swaps old text for new text automatically, making it perfect for cleaning data, fixing formatting, or updating values across your spreadsheet. It&#8217;s precise, powerful, and case-sensitive.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">What Makes SUBSTITUTE Useful<\/h5>\n\n\n\n<p>SUBSTITUTE transforms text instantly:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Replace text<\/strong> &#8211; Swap any text string for another automatically<\/li>\n\n\n\n<li><strong>Case-sensitive<\/strong> &#8211; Distinguishes between &#8220;ABC&#8221; and &#8220;abc&#8221;<\/li>\n\n\n\n<li><strong>Specific occurrences<\/strong> &#8211; Replace only the first, second, or all instances<\/li>\n\n\n\n<li><strong>Data cleaning<\/strong> &#8211; Remove unwanted characters or fix formatting<\/li>\n\n\n\n<li><strong>Bulk updates<\/strong> &#8211; Change text across hundreds of cells at once<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">The Syntax<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(text, old_text, new_text, &#91;instance_num])<\/code><\/pre>\n\n\n\n<p>Text is the original cell. Old_text is what you want to replace. New_text is the replacement. Instance_num is optional (which occurrence to replace).<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-2d3c904\" data-block-id=\"2d3c904\"><style>.stk-2d3c904 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 1: Basic Text Replacement<\/h5>\n\n\n\n<p>Replace &#8220;Inc&#8221; with &#8220;Incorporated&#8221;:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Company<\/th><\/tr><\/thead><tbody><tr><td>Tech Inc<\/td><\/tr><tr><td>Global Inc<\/td><\/tr><tr><td>DataCorp Inc<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(A2, \"Inc\", \"Incorporated\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: Tech Incorporated<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-9662ced\" data-block-id=\"9662ced\"><style>.stk-9662ced {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 2: Remove Characters<\/h5>\n\n\n\n<p>Remove dashes from phone numbers:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Phone Number<\/th><\/tr><\/thead><tbody><tr><td>555-123-4567<\/td><\/tr><tr><td>555-987-6543<\/td><\/tr><tr><td>555-456-7890<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(A2, \"-\", \"\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: 5551234567<\/p>\n\n\n\n<p>Replace dashes with empty text (&#8220;&#8221;) to delete them.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-3e0ea4a\" data-block-id=\"3e0ea4a\"><style>.stk-3e0ea4a {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 3: Replace Specific Occurrence<\/h5>\n\n\n\n<p>Replace only the second comma:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Text<\/th><\/tr><\/thead><tbody><tr><td>apple,orange,banana,grape<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(A2, \",\", \";\", 2)<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: apple,orange;banana,grape<\/p>\n\n\n\n<p>The &#8220;2&#8221; tells Excel to replace only the second comma with a semicolon.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-d92b39c\" data-block-id=\"d92b39c\"><style>.stk-d92b39c {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 4: Case-Sensitive Replacement<\/h5>\n\n\n\n<p>SUBSTITUTE respects case:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Text<\/th><\/tr><\/thead><tbody><tr><td>Send EMAIL to EMAIL address<\/td><\/tr><tr><td>Check your email daily<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(A2, \"EMAIL\", \"message\")<\/code><\/p>\n\n\n\n<p><strong>Result in B2<\/strong>: Send message to message address<\/p>\n\n\n\n<p><strong>Result in B3<\/strong>: Check your email daily (lowercase &#8220;email&#8221; unchanged)<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-8844c6f\" data-block-id=\"8844c6f\"><style>.stk-8844c6f {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 5: Clean Data from Import<\/h5>\n\n\n\n<p>Fix spacing issues in imported data:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Product Name<\/th><\/tr><\/thead><tbody><tr><td>Laptop  Pro<\/td><\/tr><tr><td>Mouse Wireless<\/td><\/tr><tr><td>Keyboard RGB<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(A2, \"  \", \" \")<\/code><\/p>\n\n\n\n<p><strong>Result in B2<\/strong>: Laptop Pro (double space becomes single)<\/p>\n\n\n\n<p>Multiple spaces reduced to one space.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-6f17e2b\" data-block-id=\"6f17e2b\"><style>.stk-6f17e2b {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Example 6: Multiple Substitutions<\/h5>\n\n\n\n<p>Replace multiple characters using nested SUBSTITUTE:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Code<\/th><\/tr><\/thead><tbody><tr><td>ABC-123-XYZ<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Formula<\/strong>: <code>=SUBSTITUTE(SUBSTITUTE(A2, \"-\", \"\"), \"ABC\", \"DEF\")<\/code><\/p>\n\n\n\n<p><strong>Result<\/strong>: DEF123XYZ<\/p>\n\n\n\n<p>First removes dashes, then replaces ABC with DEF.<\/p>\n\n\n\n<div class=\"wp-block-stackable-divider stk-block-divider stk-block stk-787df46\" data-block-id=\"787df46\"><style>.stk-787df46 {margin-bottom:0px !important;}<\/style><hr class=\"stk-block-divider__hr\"\/><\/div>\n\n\n\n<h5 class=\"wp-block-heading\">Replace All vs Specific Occurrence<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Formula<\/th><th>Replaces<\/th><\/tr><\/thead><tbody><tr><td><code>=SUBSTITUTE(A2, \"a\", \"X\")<\/code><\/td><td>All instances of &#8220;a&#8221;<\/td><\/tr><tr><td><code>=SUBSTITUTE(A2, \"a\", \"X\", 1)<\/code><\/td><td>First &#8220;a&#8221; only<\/td><\/tr><tr><td><code>=SUBSTITUTE(A2, \"a\", \"X\", 2)<\/code><\/td><td>Second &#8220;a&#8221; only<\/td><\/tr><tr><td><code>=SUBSTITUTE(A2, \"a\", \"X\", 3)<\/code><\/td><td>Third &#8220;a&#8221; only<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Common Use Cases<\/h5>\n\n\n\n<p><strong>Phone Number Formatting<\/strong> Remove or add dashes, spaces, or parentheses.<\/p>\n\n\n\n<p><strong>Data Cleaning<\/strong> Fix typos, remove unwanted characters, standardize text.<\/p>\n\n\n\n<p><strong>File Path Updates<\/strong> Change drive letters or folder names in paths.<\/p>\n\n\n\n<p><strong>Currency Conversion<\/strong> Replace $ with \u20ac or other symbols.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Practical Formulas<\/h5>\n\n\n\n<p><strong>Remove all spaces:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(A2, \" \", \"\")<\/code><\/pre>\n\n\n\n<p><strong>Replace line breaks:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(A2, CHAR(10), \" \")<\/code><\/pre>\n\n\n\n<p>CHAR(10) is a line break.<\/p>\n\n\n\n<p><strong>Standardize terms:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(SUBSTITUTE(A2, \"St.\", \"Street\"), \"Ave.\", \"Avenue\")<\/code><\/pre>\n\n\n\n<p><strong>Count occurrences of text:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=(LEN(A2)-LEN(SUBSTITUTE(A2, \"a\", \"\")))\/LEN(\"a\")<\/code><\/pre>\n\n\n\n<p>Counts how many times &#8220;a&#8221; appears.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">SUBSTITUTE vs REPLACE<\/h5>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-white-color has-palette-color-3-background-color has-text-color has-background has-link-color has-fixed-layout\"><thead><tr><th>Feature<\/th><th>SUBSTITUTE<\/th><th>REPLACE<\/th><\/tr><\/thead><tbody><tr><td>What it replaces<\/td><td>Specific text<\/td><td>Position-based<\/td><\/tr><tr><td>Case sensitivity<\/td><td>Case-sensitive<\/td><td>Position doesn&#8217;t care<\/td><\/tr><tr><td>All instances<\/td><td>Can replace all<\/td><td>Replaces one position<\/td><\/tr><tr><td>Best for<\/td><td>Text patterns<\/td><td>Fixed positions<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SUBSTITUTE: Replace &#8220;Inc&#8221; with &#8220;Corp&#8221;<\/li>\n\n\n\n<li>REPLACE: Replace characters 5-7 with &#8220;XYZ&#8221;<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Common Mistakes to Avoid<\/h5>\n\n\n\n<p><strong>Case Sensitivity Issues<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>SUBSTITUTE(A2, \"abc\", \"xyz\")<\/code> won&#8217;t replace &#8220;ABC&#8221;<\/li>\n\n\n\n<li>Text must match exactly including case<\/li>\n\n\n\n<li>Use UPPER or LOWER first if needed<\/li>\n<\/ul>\n\n\n\n<p><strong>Missing Quotes<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>New_text can be &#8220;&#8221; (empty) to delete<\/li>\n\n\n\n<li>Must use quotes even for empty: <code>SUBSTITUTE(A2, \"-\", \"\")<\/code><\/li>\n<\/ul>\n\n\n\n<p><strong>Wrong Instance Number<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Instance_num starts at 1, not 0<\/li>\n\n\n\n<li>If text appears 3 times, use 1, 2, or 3<\/li>\n\n\n\n<li>Omit instance_num to replace all<\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\">Advanced Techniques<\/h5>\n\n\n\n<p><strong>Remove last occurrence:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(A2, \"-\", \"~\", LEN(A2)-LEN(SUBSTITUTE(A2,\"-\",\"\"))<\/code><\/pre>\n\n\n\n<p>Then substitute ~ back or process it.<\/p>\n\n\n\n<p><strong>Replace only if text exists:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=IF(ISNUMBER(SEARCH(\"old\", A2)), SUBSTITUTE(A2, \"old\", \"new\"), A2)<\/code><\/pre>\n\n\n\n<p><strong>Chain multiple replacements:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, \"a\", \"1\"), \"b\", \"2\"), \"c\", \"3\")<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Data Cleaning Examples<\/h5>\n\n\n\n<p><strong>Remove extra spaces:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=TRIM(SUBSTITUTE(A2, \"  \", \" \"))<\/code><\/pre>\n\n\n\n<p>Combines SUBSTITUTE with TRIM for thorough cleaning.<\/p>\n\n\n\n<p><strong>Standardize boolean values:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(SUBSTITUTE(UPPER(A2), \"YES\", \"TRUE\"), \"NO\", \"FALSE\")<\/code><\/pre>\n\n\n\n<p><strong>Fix decimal separators:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>=SUBSTITUTE(A2, \",\", \".\")<\/code><\/pre>\n\n\n\n<p>Convert European format to US format.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Tips for Using SUBSTITUTE<\/h5>\n\n\n\n<p><strong>Test with one cell first<\/strong> Verify your replacement works before copying down.<\/p>\n\n\n\n<p><strong>Use cell references<\/strong> <code>=SUBSTITUTE(A2, B2, C2)<\/code> makes formulas dynamic.<\/p>\n\n\n\n<p><strong>Combine with other text functions<\/strong> TRIM, UPPER, LOWER work great with SUBSTITUTE.<\/p>\n\n\n\n<p><strong>Remember it&#8217;s case-sensitive<\/strong> Unlike SEARCH, case matters in SUBSTITUTE.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">Start Using It<\/h5>\n\n\n\n<p>Find cells with text you want to change. Type =SUBSTITUTE, specify the cell, what to replace, and the replacement. Press Enter and watch Excel swap the text automatically.<\/p>\n\n\n\n<p>SUBSTITUTE becomes essential for data cleaning and text transformation.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p><em>Questions about SUBSTITUTE? Want to explore REPLACE or other text functions? Let&#8217;s connect.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SUBSTITUTE replaces specific text within cells automatically, works case-sensitively, targets individual occurrences, and cleans data without changing original values.<\/p>\n","protected":false},"author":1,"featured_media":4915,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[35,36,37],"tags":[1404,1415,1420,1026,1403,1416,1421,1012,1408,1406,1414,1419,1412,1410,1413,1417,1402,1424,1405,1409,1418],"class_list":["post-4918","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-excel","category-learning","tag-advanced-search-formulas","tag-case-insensitive-search-excel","tag-excel-365-search","tag-excel-formulas-tutorial","tag-excel-search-formula","tag-excel-search-syntax","tag-excel-text-functions","tag-excel-tutorial-2025","tag-find-text-in-excel","tag-how-to-use-search-in-excel","tag-learn-search-excel","tag-partial-text-match-excel","tag-search-function-examples","tag-search-function-explained","tag-search-function-for-beginners","tag-search-function-guide","tag-search-function-in-excel","tag-search-function-position","tag-search-function-tips","tag-search-function-tutorial","tag-search-function-wildcards"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4918","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/comments?post=4918"}],"version-history":[{"count":1,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4918\/revisions"}],"predecessor-version":[{"id":4919,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/posts\/4918\/revisions\/4919"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media\/4915"}],"wp:attachment":[{"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/media?parent=4918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/categories?post=4918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandshine.eu\/index.php\/wp-json\/wp\/v2\/tags?post=4918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}