Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

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

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

  5. #5
    4 Star Lounger
    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?

  6. #6
    3 Star Lounger
    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

  7. #7
    3 Star Lounger
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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 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. #10
    4 Star Lounger
    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

  11. #11
    4 Star Lounger
    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.

  12. #12
    3 Star Lounger
    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 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"}
    Microsoft MVP - Excel

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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