Using replace to replace ONLY the first instance

[cloud2828]

I'm trying to use this formula:
REPLACE("S";1;1;"O")
To remove the first instance of S only. An example of what the column would look like:

[TABLE="width: 25"]
<tbody>[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]O
[/TD]
[/TR]
</tbody>[/TABLE]

Sadly, It is necessary that I drag the formula I have down thus resulting in all the "S" being replaced by "O".

 

[theBardd]

Try this

=IF(COUNTIF($A$2:$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))

adjust for your local settings

 

[cloud2828]

Try this

=IF(COUNTIF($A$2:$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))

adjust for your local settings

Thank you, it works perfectly!

 

[cloud2828]

Try this

=IF(COUNTIF($A$2:$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))

adjust for your local settings

I'm having an issue actually putting it into my code:
=IF(AND(AP$20=3;AP$21=4;AP$22=3;AP$23=3);AP7;IF(AND(AP$20=3;AQ$21=3;AP$22=3;AP$23=4);IF(COUNTIF($AP$7:$AP$19;"S")>1;$AP7;SUBSTITUTE($AP7;"S";"O"));IF(AND(AP$20=3;AP$21=3;AP$22=4;AP$23=3);"not sure yet";IF(AND(AP$20=4;AP$21=3;AP$22=3;AP$23=3);"not sure yet";"Z"))))

It's not replacing anything, not sure if I made an error somewhere but it seems okay to me.
Edit: Fixed it, if the Range is fixed, it doesn't work for some reason.

New formula:
[FONT=&quot]IF(COUNTIF($AP$7:$AP7;"S")>1;$AP7;SUBSTITUTE($AP7;"S";"O"))[/FONT]

 

[theBardd]

Just quickly looking, your range for the countif is absolutley fixed, in m y formula I only fixed the start cell, the end cell was moving along by row.

 

[cloud2828]

Just quickly looking, your range for the countif is absolutley fixed, in m y formula I only fixed the start cell, the end cell was moving along by row.

That did the fix, thanks again!

 

[cloud2828]

Just quickly looking, your range for the countif is absolutley fixed, in m y formula I only fixed the start cell, the end cell was moving along by row.

Would it be possible to modify this code to replace a random letter instead?

 

[cloud2828]

Would it be possible to modify this code to replace a random letter instead?

Edit: Not a random letter, but a random position, instead of just the first position. Should I make a new thread to ask this question?

 

[theBardd]

IK don't think there is a need for that, I will take a shot at it if you explain a bit more what you mean.

 

[cloud2828]

IK don't think there is a need for that, I will take a shot at it if you explain a bit more what you mean.

[TABLE="width: 25"]
<colgroup><col></colgroup><tbody>[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]P[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]S[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]T[/TD]
[/TR]
[TR]
[TD]O[/TD]
[/TR]
[TR]
[TD]S

[/TD]
[/TR]
</tbody>[/TABLE]
I have this column of letters, I need to sort them such that there are 6 P's, 4 T's, 5 S's and 7 O's. The method I used to do that was not dynamic therefore if the size was to increase and a new number of P's, S's, O's and T's is needed, my code would not be able to do it. I would also want to have a table where you can change the amount of each letter is required. I'm basically trying to create a generator. For the first part, what I did was not make it random but have it that for each consecutive column, it will replace the 1st, 2nd, 3rd letter appropriately etc. That is what I need to do, I just need to figure out a way to sort the data in such a way that it can rewrite the requirements for the column.