Ravindra BagaleCourses & study guides Track your progress

Excel SUBSTITUTE Function

By · Last updated:

Chala mitrano — sUBSTITUTE swaps words. Tell Excel what to find, what to put instead, and optionally which match number.

Fictional sample data for learning. Phone and ID cleanup (Aurangabad BPO + Austin support). Mixed ₹ and $.

Purpose

Replace specific text with new text inside a string. You can replace every match or only the Nth match.

Return value

Text with replacements applied.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

Arguments

ArgumentRequired?Description
textRequiredThe full original text.
old_textRequiredThe bit to find.
new_textRequiredThe replacement.
instance_numOptionalReplace only the Nth match; omit to replace all.

Version

All Excel versions. LibreOffice Calc compatible.

How to use — Why → What → How

Why: Aurangabad BPO sheets store phones as 98-76-543210. Austin tickets mix dashes and spaces. SUBSTITUTE standardises them before MATCH. Phone punctuation cleanup is the hero demo. Nest SUBSTITUTE for dashes and spaces, then MATCH. Mention CHAR(160) again — SUBSTITUTE is how you kill it.

What: SUBSTITUTE finds old_text and puts new_text in its place. Without instance_num it replaces every match. With instance_num it replaces only that occurrence. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Type =SUBSTITUTE(A2,"-","").
  2. Fill down.
  3. Use instance_num 1 to change only the first dash.
  4. Nest SUBSTITUTE to remove several characters.
  5. Paste values when cleaning is final.

Ravindra Bagale's Tip

Friends, sUBSTITUTE(A2,"-","") is the fast way to strip dashes from phone numbers.

Ravindra Bagale's Tip

Friends, sleep on SUBSTITUTE and rebuild example 1 tomorrow without looking. That five-minute test beats rereading the whole page.

Ravindra Bagale's Tip

Friends, after you finish SUBSTITUTE, open yesterday’s real workbook and find one place this function would have saved ten minutes. That habit turns lessons into salary skills.

Download practice file: practice-substitute.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Strip dashes

Aurangabad phones.

Phone
98-76-543210
98-11-222333
Formula: =SUBSTITUTE(A2,"-","")

Result: 9876543210

What it means: 9876543210.

2) Replace word

Austin notes.

Note
Status: Open
Status: Open
Formula: =SUBSTITUTE(A2,"Open","Closed")

Result: Status: Closed

What it means: Word swap.

3) Only first instance

First dash only.

Code
A-B-C-D
Formula: =SUBSTITUTE(A2,"-","/",1)

Result: A/B-C-D

What it means: A/B-C-D.

4) Remove spaces

Compact ID.

ID
AB 12 34
Formula: =SUBSTITUTE(A2," ","")

Result: AB1234

What it means: Spaces gone.

5) CHAR(160)

Web paste.

Name
Neha Shah
Formula: =SUBSTITUTE(A2,CHAR(160)," ")

Result: Neha Shah

What it means: Non-breaking space → normal space.

6) Nested strip

Dashes and spaces.

Phone
98-76 543210
Formula: =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

Result: 9876543210

What it means: Two cleanups nested.

Common errors and fixes

Nothing changed — old_text case/spelling mismatch. Fix: SUBSTITUTE is case-sensitive — match exactly.
Wrong instance — instance_num too high. Fix: Count matches first or omit to replace all.

Notes and tips

  • Case-sensitive.
  • instance_num is 1-based.
  • Nest to remove multiple characters.
  • BPO cleaning floors in Aurangabad and US support queues both strip phone punctuation daily. SUBSTITUTE is the workhorse before VALUE or MATCH.
  • SUBSTITUTE vs REPLACE: SUBSTITUTE finds by text; REPLACE cuts by position. SUBSTITUTE vs TEXT: TEXT formats numbers; SUBSTITUTE edits strings.
  • Fictional sample data only — practice amounts and names are made for learning.
  • After the 10 tasks, re-read the errors table once.
  • Say the business question in plain words before you type equals.
  • Keep a scrap sheet for experiments so you do not overwrite class files.
  • Fill formulas down only after the first row looks correct.
  • When two answers look possible, write both on paper then test in Excel.
  • Phone punctuation cleanup is the hero demo. Nest SUBSTITUTE for dashes and spaces, then MATCH. Mention CHAR(160) again — SUBSTITUTE is how you kill it.
  • Revisit the SUBSTITUTE errors table after the quiz — most classroom mistakes are listed there.
  • Build one brand-new example on a blank sheet from memory before you close the laptop.
  • If a result looks wrong, check spaces with LEN and case with exact equals before changing the formula.
  • Share one tip from this page with a classmate in Marathi or Hindi — teaching locks the idea.
  • Keep currency symbols consistent inside a single report column even when practice mixes ₹ $ £ € across examples.
  • When Excel versions differ at home and in class, write the fallback formula under the modern one in a comment cell.
  • Print the revision card for SUBSTITUTE and stick it near your monitor for one week of real worksheets.
  • When helping a junior, ask them to predict the result before pressing Enter — prediction builds judgement.
  • Keep a personal notebook of the one mistake you make most often with this function; review it monthly.

In real work

BPO cleaning floors in Aurangabad and US support queues both strip phone punctuation daily. SUBSTITUTE is the workhorse before VALUE or MATCH. Phone punctuation cleanup is the hero demo. Nest SUBSTITUTE for dashes and spaces, then MATCH. Mention CHAR(160) again — SUBSTITUTE is how you kill it. Classroom drill: write three brand-new SUBSTITUTE formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose (wrong case, wrong start, extra space) and watch the error; recovering from that break is half of interview readiness.

How it compares

SUBSTITUTE vs REPLACE: SUBSTITUTE finds by text; REPLACE cuts by position. SUBSTITUTE vs TEXT: TEXT formats numbers; SUBSTITUTE edits strings.

Interview corner

Case-sensitive?
Yes.
Replace only second dash?
instance_num 2.
Strip all dashes?
new_text empty "".

REPLACE, TRIM, SUBSTITUTE, TEXT

Practice tasks (10)

  1. Task 1. Strip dashes from 98-76-543210.
    Hint: replace with empty text.
  2. Task 2. Change Open to Closed.
    Hint: word swap.
  3. Task 3. Only first dash to slash on A-B-C-D.
    Hint: instance 1.
  4. Task 4. Remove spaces from AB 12 34.
    Hint: old_text is space.
  5. Task 5. Nest two SUBSTITUTE calls.
    Hint: dashes and spaces.
  6. Task 6. Explain case-sensitivity in a note.
    Hint: Open vs open.
  7. Task 7. Fill clean phones for three rows.
    Hint: fill down.
  8. Task 8. CHAR(160) demo if you can paste one.
    Hint: web space.
  9. Task 9. Count matches before picking instance_num.
    Hint: think first.
  10. Task 10. Interview: empty new_text means?
    Hint: delete.

Quick quiz

Q1. SUBSTITUTE("A-B","-","") returns:
Q2. Case:
Q3. Only first match:
Q4. CHAR(160) cleanup uses:
Q5. Returns:

Answers: Q1=AB, Q2=Sensitive, Q3=instance_num 1, Q4=SUBSTITUTE, Q5=Text (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: SUBSTITUTE
SUBSTITUTE revision card