SUBSTITUTE and REPLACE in Excel - Dashboard Guide

Dashboard Mode - SUBSTITUTE and REPLACE

Goal

Find and fix text errors in product codes by replacing wrong parts or substituting specific words.

Sample Data

Row	Product Code	Issue Description
2	AB12X45	Replace 'X' with '9'
3	CD34Y78	Substitute 'Y' with '8'
4	EF56Z90	Replace 'Z' with '7'
5	GH78X12	Substitute 'X' with '5'
6	IJ90Y34	Replace 'Y' with '6'

Dashboard Components

• Corrected Code (Replace): For rows needing REPLACE function to fix a single character at a known position.
  =REPLACE(B2,5,1,"9") (Example for row 2, replaces 5th character 'X' with '9')
• Corrected Code (Substitute): For rows needing SUBSTITUTE function to replace all instances of a character.
  =SUBSTITUTE(B3,"Y","8") (Example for row 3, substitutes all 'Y' with '8')
• Summary Table: Shows original code, issue, and corrected code side by side using the correct formula for each row.

Dashboard Layout

+----------------------+------------------------+------------------------+
| Original Product Code | Issue Description      | Corrected Code         |
+----------------------+------------------------+------------------------+
| AB12X45              | Replace 'X' with '9'   | =REPLACE(B2,5,1,"9") |
| CD34Y78              | Substitute 'Y' with '8'| =SUBSTITUTE(B3,"Y","8") |
| EF56Z90              | Replace 'Z' with '7'   | =REPLACE(B4,5,1,"7") |
| GH78X12              | Substitute 'X' with '5'| =SUBSTITUTE(B5,"X","5") |
| IJ90Y34              | Replace 'Y' with '6'   | =REPLACE(B6,5,1,"6") |
+----------------------+------------------------+------------------------+ 

Interactivity

Filter by Issue Description to see only rows needing SUBSTITUTE or REPLACE. When filtered, the Corrected Code column updates to show only relevant formulas and results.

Self Check

If you add a filter to show only rows with 'Substitute' in Issue Description, which Corrected Code formulas remain visible?

Answer: Only the rows using the SUBSTITUTE function formulas remain visible (rows 3 and 5).

Key Result

Dashboard shows original product codes, text issues, and corrected codes using SUBSTITUTE or REPLACE formulas.