SUBSTITUTE
TextintermediateSUBSTITUTE finds every occurrence of a text string and replaces it with another. Unlike Find & Replace (Ctrl+H), SUBSTITUTE works in formulas so you can transform data without modifying the source. It's case-sensitive and can target a specific occurrence if needed.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Parameters
textThe text or cell to modify.old_textThe text to find.new_textThe replacement text.instance_num?Which occurrence to replace. If omitted, replaces all.? = optional parameter
Worked Example
Replace 'Software' with 'SaaS' in the category field.
| Row numbers | C | |
|---|---|---|
| Header row | category | after SUBSTITUTE |
| 2 | Software | SaaS |
| 3 | Consulting | Consulting |
| 4 | Office Supplies | Office Supplies |
| 5 | Software | SaaS |
Formula
=SUBSTITUTE(A2, "Software", "SaaS")Result
SaaSSUBSTITUTE looks at A2, finds the text 'Software', and replaces it with 'SaaS'. In this sample, A2 changes from 'Software' to 'SaaS'.
Practice Exercises
2 exercises - work through each one using the data table below
Question
The company is rebranding 'Operations' to 'Business Operations' in all reports. Write a formula that would transform the department name. What's the result for a row with department 'Operations'?
Dataset
Showing rows 1-10
Download CSV| Row numbers | A | B | C |
|---|---|---|---|
| Header row | |||
| 2 | INV-1001 | Cloudline Software | Engineering |
| 3 | INV-1002 | Apex Office Supply | Operations |
| 4 | INV-1003 | Summit Consulting Group | Finance |
| 5 | INV-1004 | Meridian Travel Co. | Sales |
| 6 | INV-1005 | Forge & Frame Creative | Marketing |
| 7 | INV-1006 | NovaTech Solutions | Engineering |
| 8 | INV-1007 | Atlas Hardware Co. | Operations |
| 9 | INV-1008 | Clearview Data Systems | Engineering |
| 10 | INV-1009 | Redstone Marketing | Marketing |
| 11 | INV-1010 | Keystone Legal Advisors | Finance |
| 12 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Question
Remove all spaces from the vendor name 'Summit Consulting Group' using SUBSTITUTE. What's the result?
Dataset
Showing rows 1-10
Download CSV| Row numbers | A | B |
|---|---|---|
| Header row | ||
| 2 | INV-1001 | Cloudline Software |
| 3 | INV-1002 | Apex Office Supply |
| 4 | INV-1003 | Summit Consulting Group |
| 5 | INV-1004 | Meridian Travel Co. |
| 6 | INV-1005 | Forge & Frame Creative |
| 7 | INV-1006 | NovaTech Solutions |
| 8 | INV-1007 | Atlas Hardware Co. |
| 9 | INV-1008 | Clearview Data Systems |
| 10 | INV-1009 | Redstone Marketing |
| 11 | INV-1010 | Keystone Legal Advisors |
| 12 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Want more practice?
Test Me mode randomizes exercises across all formulas without telling you which formula to use.