Excel Function

SUBSTITUTE

Substitutes occurrences of text with new text.

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

Arguments

text

Required

Enter a text value (wrap with quotes when needed).

old_text

Required

Enter a text value (wrap with quotes when needed).

new_text

Required

Enter a text value (wrap with quotes when needed).

instance_num

Optional

Enter a numeric value.

Practice Preview

Use SUBSTITUTE to return the result with these settings (Text: B2 (cell B2 (Name in column B)), Old text: " ", New text: "", Instance number: 1).

Example Table

#
AEmployee ID
BName
CEmail
DPayment Amount
EAmount Text
2E001Taro Tanakat.tanaka@example.com12001200
3E002Hanako Suzukih.suzuki@example.com980980
4E003Ken Satok.sato@example.com24502450
5E004Aya Takahashia.takahashi@example.com30003000
6
7
8

How to Use (Formula)

=SUBSTITUTE(B2, " ", "", 1)

Result

TaroTanaka

Practice This Function

Start Function PracticeBack to Function List

SUBSTITUTE FAQ

How do I use SUBSTITUTE in Excel?

Use SUBSTITUTE when you want to replace specific text values. Enter Text, Old text, New text in that order, then add optional arguments when needed.

Where can I practice SUBSTITUTE?

Use the "Start Function Practice" button to jump straight into SUBSTITUTE drills.

What arguments does SUBSTITUTE use?

The main arguments are Text, Old text, New text, Instance number. Start with the required ones, then add optional arguments if needed.