How to use SUBSTITUE function in Excel?
In this article, I introduce how to use the SUBSTITUE function in Excel.
SUBSTITUE function
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Case 1 Replace specific character with other in specific location
=SUBSTITUTE(text, old_text, new_text, [instance_num])
If you want to replace a string with another in a specific location, please do as these:
Select a cell which you will place the result and type this formula=SUBSTITUTE(A1, "1", "2", 1), then the first occurrence of number 1 has been replace with 2.
If you want to replace the second occurrence of number 1 with 2, use this formula =SUBSTITUTE(A1, "1", "2", 2).
If you want to replace all number 1 with 2, use this =SUBSTITUTE(A1, "1", "2").
Case 2 Replace multiple strings
If you want to replace multiple characters in a string at once time, you can use this formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"PR","Project "),"ML","Milestone "),"T","Task ").
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
