# Thread: Find 2 letters formula (Excel xp)

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

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

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

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

5. ## Re: Find 2 letters formula (Excel xp)

thank you Legare!

what about the substitute part? can i do more than 7 substitutes at once?

6. ## Re: Find 2 letters formula (Excel xp)

=IF(COUNT(SEARCH({"B","C"},AF48)),SUBSTITUTE(SUBST ITUTE(AF48,"B","S"),"C","S"),"")

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

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

9. ## Re: Find 2 letters formula (Excel xp)

1) you forgot a dbl-quote 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

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

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

12. ## Re: Find 2 letters formula (Excel xp)

Activate Insert|Name|Define.
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"}

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•