Ravindra BagaleCourses & study guides Track your progress

Excel REPLACE Function

By · Last updated:

Chala mitrano — rEPLACE edits by position. Start here, remove N letters, drop new text in.

Fictional sample data for learning. Masking IDs and fixing fixed-width codes (Pune HR + Frankfurt payroll). Mixed ₹ and €.

Purpose

Replace characters at a fixed position with new text — by start and length, not by matching words.

Return value

Text after the positional replacement.

Syntax

REPLACE(old_text, start_num, num_chars, new_text)

Arguments

ArgumentRequired?Description
old_textRequiredOriginal text.
start_numRequired1-based start of the part to overwrite.
num_charsRequiredHow many characters to remove before inserting new_text.
new_textRequiredText to insert at that spot.

Version

All Excel versions. LibreOffice Calc compatible.

How to use — Why → What → How

Why: Pune HR shows only last 4 of an employee code on printed badges. Frankfurt payroll masks IBAN display the same way. REPLACE edits by known positions. Positional REPLACE is for masks and fixed-width fixes. Stress privacy: fictional IDs only. Sketch character positions on paper before typing start_num.

What: REPLACE removes num_chars characters starting at start_num and inserts new_text there. Unlike SUBSTITUTE, it does not search for a word. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Type =REPLACE(A2,1,8,"**** **** ").
  2. Confirm start and length on paper first.
  3. Fill down for a badge list.
  4. Combine with RIGHT to keep the visible tail.
  5. Never use real sensitive IDs in practice files.

Ravindra Bagale's Tip

Friends, masking Aadhaar-style display (fictional) uses REPLACE to keep last 4 and star the rest — never store real Aadhaar in class files.

Ravindra Bagale's Tip

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

Ravindra Bagale's Tip

Friends, after you finish REPLACE, 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-replace.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Mask front

Pune badge (fictional codes).

EmpCode
EMP123456789
EMP987654321
Formula: =REPLACE(A2,1,8,"********")

Result: ********6789

What it means: Front masked.

2) Fix year in code

Frankfurt batch code.

Batch
DE-2024-091
DE-2024-092
Formula: =REPLACE(A2,4,4,"2026")

Result: DE-2026-091

What it means: Year becomes 2026.

3) Insert by replacing 0 chars

Insert marker.

Code
ABCDE
Formula: =REPLACE(A2,3,0,"-")

Result: AB-CDE

What it means: AB-CDE — zero removed, hyphen inserted.

4) With RIGHT

Show last 4 only style.

EmpCode
EMP123456789
Formula: =REPLACE(A2,1,LEN(A2)-4,REPT("*",LEN(A2)-4))

Result: *******6789

What it means: Stars then last 4 (pattern).

5) Shorten middle

Remove two chars.

Code
AAXXBB
Formula: =REPLACE(A2,3,2,"")

Result: AABB

What it means: XX removed.

6) € label tweak

Currency tag.

Label
AMT-EUR-100
Formula: =REPLACE(A2,5,3,"€")

Result: AMT-€-100

What it means: EUR → €.

About this data. Small demo rows for learning. Real sheets are longer; the same formula pattern scales when columns are clean.

Common errors and fixes

#VALUE! — start_num < 1 or num_chars < 0. Fix: Use valid positive start and non-negative length.
Wrong mask — Miscounted length. Fix: Sketch positions on paper first.

Notes and tips

  • Positional, not search-based.
  • num_chars 0 inserts without deleting.
  • Keep practice IDs fictional.
  • Badge printing in Pune and IBAN display rules in Frankfurt both need positional edits. Stress privacy: fictional data only in class.
  • REPLACE vs SUBSTITUTE: position vs search. REPLACE vs MID: MID extracts; REPLACE rewrites.
  • 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.
  • Positional REPLACE is for masks and fixed-width fixes. Stress privacy: fictional IDs only. Sketch character positions on paper before typing start_num.
  • Revisit the REPLACE 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 REPLACE 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

Badge printing in Pune and IBAN display rules in Frankfurt both need positional edits. Stress privacy: fictional data only in class. Positional REPLACE is for masks and fixed-width fixes. Stress privacy: fictional IDs only. Sketch character positions on paper before typing start_num. Classroom drill: write three brand-new REPLACE 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

REPLACE vs SUBSTITUTE: position vs search. REPLACE vs MID: MID extracts; REPLACE rewrites.

Interview corner

Search or position?
Position.
Insert without deleting?
num_chars 0.
Sensitive data in class?
Always fictional.

SUBSTITUTE, MID, LEFT, RIGHT

Practice tasks (10)

  1. Task 1. Mask first 8 of EMP123456789.
    Hint: start 1.
  2. Task 2. Change 2024 to 2026 in DE-2024-091.
    Hint: start 4 length 4.
  3. Task 3. Insert hyphen with num_chars 0.
    Hint: ABCDE demo.
  4. Task 4. Remove XX from AAXXBB.
    Hint: start 3 length 2.
  5. Task 5. Note privacy rule for class files.
    Hint: fictional only.
  6. Task 6. Fill masks for two emp codes.
    Hint: fill down.
  7. Task 7. Compare REPLACE vs SUBSTITUTE in a sentence.
    Hint: position vs search.
  8. Task 8. Sketch positions on paper before formula.
    Hint: avoid miscount.
  9. Task 9. € label tweak example.
    Hint: example 6.
  10. Task 10. Interview: insert without delete?
    Hint: 0.

Quick quiz

Q1. REPLACE edits by:
Q2. num_chars 0 means:
Q3. start_num begins at:
Q4. Word swap tool is usually:
Q5. Practice IDs should be:

Answers: Q1=Position, Q2=Insert only, Q3=1, Q4=SUBSTITUTE, Q5=Fictional (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: REPLACE
REPLACE revision card