Forum Discussion

Norman_Glenn's avatar
Norman_Glenn
Brass Contributor
Jan 27, 2022
Solved

Formula Excel

I have a formula that can spot a word in a Cell and add a letter to a text in another cell. ie if cell A3 shows WT and cell B3 shows Card then change A3 to WTC.... What I'm need to do now is have a fo...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jan 28, 2022

    Norman_Glenn Ah, now I see the formula you are using.  Basically you can just repeat the calculation you do to find "CARDBOARD" and use it for "GOOD":

    =B19&IF(ISNUMBER(SEARCH("CARDBOARD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"C","")&IF(ISNUMBER(SEARCH("GOOD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"G","")

    That said, this formula also checks and only appends "C" if the cell in column B is one of the following: FS, MT, JS, or HS.  I just copied that IF() condition exactly and only changed the SEARCH to be for "GOOD".  My question is if appending a "G" should also be only for those cases.  If so you could make it easier to read and slightly more efficient:

     

     

    =B19&IF((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS"),
           IF(ISNUMBER(SEARCH("CARDBOARD",C19)),"C","")
          &IF(ISNUMBER(SEARCH("GOOD",C19)),"G","")
      ,"")

     

     

    Also right now it will append a "C" if appropriate and THEN a "G" if appropriate (i.e. in some case it appends "CG".  I believe that is your intent.

mtarler's avatar
mtarler
Silver Contributor
Jan 27, 2022
I don't see any text manipulation in that formula. i see you subtracting 2 numbers based on INDEX() of 2 arrays. That said, you are looking up if $A20=... and then INDEX based on the location of $A20 in the array $A$20:$A$60. I get that you are probably copying down so $A20 increments down $A21, $A22, etc... but why do you need the MATCH part? Can't you just use $W20 and $S20 instead of those INDEX(MATCH())? Instead of just the MATCH condition you could use ROW($A1) and as that copies down it will increment accordingly to give you 1,2,3.... or as a more generic solution (that allows you to copy it to rows < 20 you can use ROW($A20)-ROW($A$19) and if you HAVE to copy down to row 1 you can use ROW($A20) - ROW($A$20) + 1.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 28, 2022

      Norman_Glenn Ah, now I see the formula you are using.  Basically you can just repeat the calculation you do to find "CARDBOARD" and use it for "GOOD":

      =B19&IF(ISNUMBER(SEARCH("CARDBOARD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"C","")&IF(ISNUMBER(SEARCH("GOOD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"G","")

      That said, this formula also checks and only appends "C" if the cell in column B is one of the following: FS, MT, JS, or HS.  I just copied that IF() condition exactly and only changed the SEARCH to be for "GOOD".  My question is if appending a "G" should also be only for those cases.  If so you could make it easier to read and slightly more efficient:

       

       

      =B19&IF((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS"),
           IF(ISNUMBER(SEARCH("CARDBOARD",C19)),"C","")
          &IF(ISNUMBER(SEARCH("GOOD",C19)),"G","")
      ,"")

       

       

      Also right now it will append a "C" if appropriate and THEN a "G" if appropriate (i.e. in some case it appends "CG".  I believe that is your intent.

      • Norman_Glenn's avatar
        Norman_Glenn
        Brass Contributor
        Jan 28, 2022
        Excellent, just as I wanted thank you.
  • Norman_Glenn's avatar
    Norman_Glenn
    Brass Contributor
    Jan 28, 2022
    Hi mtarler.. I'm using that fourmla to look down column B and see if any cell says cardboard and if it does then in column A Use the text from column B but put C at end to show its card. What I need to add to that fourmla is to do as it is but also look down column C and if it any cells say good then place a G at end of text in column A.. iv attached below what I'm doing so far..

Resources