using excel to replace a particular character, if it is present

Hello all,

1st time post, so apologies if it is in the wrong section etc.

I am attempting to write a formula to change a character in text, but only if it is present.

So, for example, the text is "AB1oABCD" it should be AB10ABCD [The o replaces with a zero]

The logic is something along the lines of 'If the 4th character is the letter o, replace it with the number 0, if it is not, then leave the text as it is'

i have tried using the LEFT and RIGHT functions to point an IF function at the 4th character, then if the IF says there is a o in the 4th character position, REPLACE is used to change it to a 0. I can't seem to get it to work. I would like to avoid VBA and macros if possible.

Can anyone help ?

Thanks in advance !

EDIT: Solved, well i think so, see my post further down.

Hi adx1000 and welcome to the forum,
Try

Please Login or Register  to view this content.

Originally Posted by miamihurricanes

Assuming that your data starts in colum A1, and you want to avoid macros what I would do is

in column B....=left(A1,3)...then copy and paste special "values"
in column C....=mid(A1,4,1)..then copy and paste special "values." At this point I would highlight column C and find and replace "o" with "0."
in column D...=mid(A1,5,10) I don't know how long the average values are but from your example it seems that this should work. Again, copy and paste special "values."

Finally, in column E =concantenate(B1,C1,D1)


Hope this works!

Thanks for the reply, I can see that this would work, essentially splitting out the 4th character, changing it, then concantenate it all back together.

I just figured that there is a way of pointing excel functions at a partcular character in a cell, then instructing it to change that character from "o" to "0".

EDIT: Just tried this, and it does indeed work. However it seems a little long winded for a replace. I will have an experiment with the MID function however. Thanks.
If anyone has any other ideas, then im all ears !

EDIT 2: I think i may have cracked it, using the MID function.

along the lines of =IF((MID(A1,4,1))="o",REPLACE(A1,4,1,0),A1)

Thanks for the help everyone. If there is a more graceful way, let me know !