How to Replace a Particular Character with Number in Microsoft Excel
If you are looking for a formula to replace a particular character on specific position with a specific number then this article is for you. In this article, we will use a combination of IF, MID & REPLACE functions to substitute the particular character with zero.
Question): I want a formula to look in for “o” at 4th position & then replace it with “0” (zero). If there is any other character or number at 4th position then formula should do nothing. I hope my explanation is good enough to understand what my output is. One more thing I would like to add is formula should not be case-sensitive i.e. whether character “o” or “O” is in small letter or in upper letter.
Following is the snapshot of preferred output:
- We will use IF formula to check if the 4th position is “o” or anything else.
- If found then Replace formula will be used to replace “o” with “0” (zero)
- In cell B2, the formula is
In case you have a similar requirement but the position & specific character used in this example are changed then you can easily tweak the formula & should be able to get the desired output.
In this way, we can replace a specific character on specific position with another character.