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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube