Results 1 to 13 of 13

20030706, 01:16 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Find 2 letters formula (Excel xp)
I need to FIND a B or a C in AF48, and change it to an S if it's either of those letters. If it's not then i need to make the cell blank.
=if(iserror(find("C",AF48)),"","S")
i don't know how to add the B to the formula. thank you for the help.

20030706, 02:07 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Find 2 letters formula (Excel xp)
You need to use a macro if you want to change Cell AF48.
This will look at the value in in AF48 and if it has a "B" it will change the first "B" to an "S". If it is does NOT have a "B" it will check for a "C". If it has a "C" it will replace the first "C" with an "S". If it is no Bs or Cs ir will clear the cell contents.
<pre>Sub jha900()
Dim rng As Range
Set rng = Range("AF489")
If Len(Application.WorksheetFunction. _
Substitute(rng.Value, "B", "", 1)) < _
Len(rng.Value) Then 'We have a "B"
rng.Value = Application.WorksheetFunction. _
Substitute(rng.Value, "B", "S", 1)
ElseIf Len(Application.WorksheetFunction. _
Substitute(rng.Value, "C", "", 1)) < _
Len(rng.Value) Then 'We have a "C"
rng.Value = Application.WorksheetFunction. _
Substitute(rng.Value, "C", "S", 1)
Else
rng.ClearContents
End If
End Sub</pre>
You could make a substitute in a DIFFERENT cell based on the AF48 contents (though you couldn't "blank it" only make it a null string).
This formula will do what you ask to whatever cell you put it in, though if you put it into AF48 you will have a circular reference. That is why you need a macro.
=IF(LEN(SUBSTITUTE(AF48,"B","",1))<LEN(AF48),SUBST ITUTE(AF48,"B","S",1),IF(LEN(SUBSTITUTE(AF48,"C"," ",1))<LEN(AF48),SUBSTITUTE(AF48,"C","S",1),"") )
If you want to replace all Bs or all Cs remove the ",1" in the 2 substitutes (both formula and macro). This tells it to change ONLY the first occurrence. WIth no parameter ALL will be changed.
Steve

20030706, 02:25 #3
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Find 2 letters formula (Excel xp)
ok, thank you very much.
I am also trying to use the substitute in a formula. I have 7 substitutes, but when i add one more it gives me an error.
=IF(ISTEXT(G166),substitute(substitute(substitute( substitute(substitute(substitute(substitute(g166," B",""),"C",""),"D",""),"F",""),J","A"),"K","B"),"L ","C","")
i need to add a G,""
is that possible or am i doing this the wrong way?

20030706, 03:12 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find 2 letters formula (Excel xp)
This formula should do what you asked:
<pre>=IF(ISERROR(FIND("B",AF48)),IF(ISERROR(FIND(" C",AF48)),"","S"),"S")
</pre>
Legare Coleman

20030706, 03:25 #5
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Find 2 letters formula (Excel xp)
thank you Legare!
what about the substitute part? can i do more than 7 substitutes at once?

20030706, 06:38 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find 2 letters formula (Excel xp)
=IF(COUNT(SEARCH({"B","C"},AF48)),SUBSTITUTE(SUBST ITUTE(AF48,"B","S"),"C","S"),"")
Microsoft MVP  Excel

20030706, 06:48 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find 2 letters formula (Excel xp)
It's a bit unclear what the substitutions are... It seems that you want to substitute first a "" for B, C, D, F, and G, then A for J, B for K, and L for C. Right?
Microsoft MVP  Excel

20030706, 08:50 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Find 2 letters formula (Excel xp)
This formula:
=IF(AND(ISERROR(FIND("B",AF48)),ISERROR(FIND("C",A F48))),"","S")
is a little cleaner, though I thought he wanted to actually replace the letters, all this will do is give an "S" or a "" depending on whether it contains B or C.
Steve

20030706, 09:10 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Find 2 letters formula (Excel xp)
1) you forgot a dblquote before the "J" in your formula:
2) I think the problem has to do with the IF statement and how many "conditionals" it allows (even though you are NOT doing 7 IFs.) Get rid of the IF statement, I don't hink you need it:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G166,"B","" ),"C",""),"D",""),"F",""),"J","A"),"K","B"),"L","C "),"G","")
will work, though instead of giving a FALSE for a number or error it will give the number and error directly.
You could alsways copy this formula elsewhere and then use something like (replace <cell reference> with where you put the above formula)
=IF(istext(g166),<cell reference>)
to get the same result without the limitation of the number of conditions.
Steve

20030706, 10:38 #10
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Find 2 letters formula (Excel xp)
thank you for the help. If G166 has nothing in it i get a zero so that's why i had the if(istext...
how can i include that condition in the formula? thanks

20030706, 10:41 #11
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Find 2 letters formula (Excel xp)
yes, that's right. I have to allow for if G166 is blank then the cell should return blank. thank you for the help.

20030706, 13:34 #12
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find 2 letters formula (Excel xp)
Activate InsertNameDefine.
Enter SubstSubSet as name in the Names in Workbook box.
Enter the following formula in the Refers to box:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(!$A1,"B",""),"C",""),"D",""),"F",""),"G","")
Click OK.
Let A1:A2 house...
{"BLC67JK";"YBCL8JK"}
In B1 enter & copy down:
=IF(COUNT(SEARCH({"B","C","D","F","G"},A1))*(A1<>" "),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SubstSubSet,"J ","A"),"K","B"),"L","C"),"")
The results in B1:B2 would be...
{"C67AB";"YC8AB"}Microsoft MVP  Excel

20030706, 14:58 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Find 2 letters formula (Excel xp)
Read the second part of my last message. Make the substitution formula an intermediate and then call the int form in the "real formula"
Steve