SUBSTITUTE and REPLACE in Google Sheets - Dashboard Guide

Dashboard Mode - SUBSTITUTE and REPLACE

Goal

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

Sample Data

Row	Product Code	Description
2	AB123XX45	Blue Shirt Shirt
3	CD456YY78	Red Pants Pants
4	EF789ZZ90	Green Hat
5	GH012XX34	Yellow Shirt Shirt
6	IJ345YY67	Black Pants

Dashboard Components

• KPI Card 1: Corrected Product Code (Replace wrong "XX" or "YY" with "00")
  =IF(ISNUMBER(SEARCH("XX", B2)), REPLACE(B2, SEARCH("XX", B2), 2, "00"), IF(ISNUMBER(SEARCH("YY", B2)), REPLACE(B2, SEARCH("YY", B2), 2, "00"), B2))
  Shows product codes with "XX" or "YY" replaced by "00".
• KPI Card 2: Clean Description (Remove repeated word "Shirt" or "Pants" once)
  =SUBSTITUTE(C2, " Shirt", "", 1) for "Shirt" rows and =SUBSTITUTE(C2, " Pants", "", 1) for "Pants" rows.
  Removes the second repeated word.
• Table: Shows original and corrected product codes and descriptions side by side.
  Columns:
  - Original Product Code (B2:B6)
  - Corrected Product Code (formula above)
  - Original Description (C2:C6)
  - Clean Description (formula above)

Dashboard Layout

+----------------------+-----------------------+
| Corrected Product    | Clean Description      |
| Code (KPI Card 1)   | (KPI Card 2)           |
+----------------------+-----------------------+
| Original & Corrected Product Codes Table       |
| +--------------------------------------------+ |
| | Original Code | Corrected Code             | |
| +--------------------------------------------+ |
| | ...          | ...                        | |
| +--------------------------------------------+ |
+-----------------------------------------------+
| Original & Clean Descriptions Table            |
| +--------------------------------------------+ |
| | Original Desc | Clean Desc                 | |
| +--------------------------------------------+ |
| | ...          | ...                        | |
| +--------------------------------------------+ |
+-----------------------------------------------+

Interactivity

Add a filter to select product codes containing "XX" or "YY". When filtered, KPI Card 1 updates to show only corrected codes for those filtered rows. The tables also update to show only filtered rows with original and corrected data. KPI Card 2 updates descriptions accordingly.

Self Check

If you add a filter to show only product codes containing "XX", which rows appear and how do the corrected product codes look?

Answer: Rows 2 and 5 appear. The corrected product codes replace "XX" with "00", so "AB123XX45" becomes "AB1230045" and "GH012XX34" becomes "GH0120034".

Key Result

Dashboard fixes product codes by replacing wrong parts and cleans repeated words in descriptions using SUBSTITUTE and REPLACE.