Replace a Particular Character at Specific Position

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 4^th position & then replace it with “0” (zero). If there is any other character or number at 4^th 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 4^th 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
=IF((MID(A2,4,1))="o",REPLACE(A2,4,1,0),A2)

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.