Excel SUBSTITUTE: Find & Replace in Formulas
SUBSTITUTE Function is a Excel function that substitute searches a2 for every occurrence of a dash (-) and replaces it with a forward slash (/). Formula Genius generates and validates this formula automatically from a plain-English prompt.
SUBSTITUTE replaces text within a cell using a formula. Clean messy data, standardize formats, and fix text issues automatically.
The Formula
"Replace all dashes with forward slashes in a date column"
=SUBSTITUTE(A2, "-", "/")SUBSTITUTE searches A2 for every occurrence of a dash (-) and replaces it with a forward slash (/). Unlike Find & Replace, this works as a formula — the original data stays intact.
Step-by-Step Breakdown
- SUBSTITUTE(text, old_text, new_text, [instance_num])
- A2 is the cell containing text to modify
- "-" is the text to find
- "/" is the replacement text
- Optional instance_num: specify which occurrence to replace (omit to replace all)
Edge Cases & Warnings
- SUBSTITUTE is case-sensitive: 'ABC' won't match 'abc'
- To replace multiple different characters, nest SUBSTITUTE: =SUBSTITUTE(SUBSTITUTE(A2, "-", "/"), ".", "/")
- Returns the original text unchanged if old_text isn't found
- instance_num must be positive — 0 returns an error
Examples
"2026-01-15"
2026/01/15"Hello World Hello"
Hello World Hello (replace all)"=SUBSTITUTE(A2, "Hello", "Hi", 1)"
Hi World Hello (first only)Frequently Asked Questions
What's the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds text by content. REPLACE replaces by character position. Use SUBSTITUTE when you know what text to replace. Use REPLACE when you know the position.
How do I remove characters from text?
Replace with empty string: =SUBSTITUTE(A2, "-", "") removes all dashes.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.