How to replace first n character or nth occurrence of character with another in Excel?
In Excel, we usually find and replace a string by the Find and Replace function, but if you are in one of below cases, the Find and Replace function cannot help you.
Replace first n char with another
Replace nth occurrence of specific char with anothe
In this article, I introduce some simple formulas to solve above questions.
Replace first n char with another
To replace the first n characters with another string, you just need the Replace function.
Select a cell you will place the formula, and type this =REPLACE(A1,1,3,"KTE"), then drag fill handle over the cells you need.
In the formula, A1 is the cell you use, 1 and 3 indicate to replace the first 3 characters, KTE is the new string. If you want to replace 3 characters from the second character, use the formula like this =REPLACE(A1,2,3,"KTE").
Replace nth char with another
If you just want to replace nth occurrence of a specific character with another string, you can apply the SUBSTITUE function.
Select a cell you will place the formula, and type this =SUBSTITUTE(A2, "T", "Task", 2), then drag fill handle over the cells you need.
The formula is case sensitive.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
