Substitute Character

[Rubber Beaked Woodpecker]

Hi all

I am having a little trouble with a substitute formula in which I am trying to achieve the following;

If the 11th character in cell A1 ="k" I would like to substitute the 12th character with "".

Any advice please

Many thanks

RBW

 

[Scott Huish]

=IF(MID(A1,11,1)="k",REPLACE(A1,12,1,""),"")

 

[lrobbo314]

Book1
	A	B	C
1	abcdefghijkLmnopqrstuvwxyz		abcdefghijkmnopqrstuvwxyz
Sheet1

Cell Formulas
Range	Formula
C1	C1	=REPLACE(A1,12,1,"")

 

[Joe4]

Book1
	A	B	C
1	abcdefghijkLmnopqrstuvwxyz		abcdefghijkmnopqrstuvwxyz
Sheet1

Cell Formulas
Range	Formula
C1	C1	=REPLACE(A1,12,1,"")

I think you missed the "IF" part of the formula.
That would replace it every time, regardless of what the 11 character is.

 

[Peter_SSs]

=IF(MID(A1,11,1)="k",REPLACE(A1,12,1,""),"")

I'm assuming that if the 11th character is not "k" then nothing is replaced rather than everything.

=IF(MID(A1,11,1)="k",REPLACE(A1,12,1,""),A1)

 

[Scott Huish]

Good call

 

[Rick Rothstein]

This should also work...

=REPLACE(A1,12,MID(A1,11,1)="k","")

 

[Rubber Beaked Woodpecker]

Thank you all! Have been a great help

 

[Peter_SSs]

Cheers. Glad we could help. Thanks for the follow-up.