# 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.

1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...