Using replace to replace ONLY the first instance

cloud2828

New Member
Joined
Jul 16, 2019
Messages
43
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
Joined
Jan 21, 2012
Messages
912
Try this

Code:
=IF(COUNTIF($A$2:$A2,"S")>1,A2,SUBSTITUTE(A2,"S","O"))
adjust for your local settings
 

cloud2828

New Member
Joined
Jul 16, 2019
Messages
43
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
Joined
Jan 21, 2012
Messages
912
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
Joined
Jul 16, 2019
Messages
43
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
Joined
Jul 16, 2019
Messages
43
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
Joined
Jan 21, 2012
Messages
912
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
Joined
Jul 16, 2019
Messages
43
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top