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:

cloud2828

New Member
Try this

Code:
=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]
 
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.
Would it be possible to modify this code to replace a random letter instead?
 

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.
 

Some videos you may like

This Week's Hot Topics

Top