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 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:

 

img1

 

  • 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
  • =IF((MID(A2,4,1))=”o”,REPLACE(A2,4,1,0),A2)

 

img2

 

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.

 

image 7

Download – How to replace a particular character if it is present – xlsx



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>