# Using replace to replace ONLY the first instance

#### cloud2828

##### New Member
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:

 T O O P P T S S P O T O O

<tbody>
</tbody>

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

Last edited:

#### theBardd

##### Rules violation
Try this

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

#### cloud2828

##### New Member
Try this

Code:
``=IF(COUNTIF(\$A\$2:\$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))``
Thank you, it works perfectly!

#### cloud2828

##### New Member
Try this

Code:
``=IF(COUNTIF(\$A\$2:\$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))``
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]

Last edited:

#### theBardd

##### Rules violation
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

##### New Member
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

##### New Member
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

##### New Member
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?

Last edited:

#### theBardd

##### Rules violation
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

##### New Member
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.
 S T P S O T T P P P T S O T O T S S S O O T O S

<colgroup><col></colgroup><tbody>
</tbody>
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.