SOLVED

Formula Excel

Brass Contributor
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 formula that can spot an other word that is "Bad" in B3 and again change cell A3 to WTCB. So it's finding two Words in one cell... iv attached a formula that I'm using. Just need to know how to search and edit for a 2nd word..... in photo iv all cells locked.. only cell that is locked is cell W17
5 Replies
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.
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..

Here is the attachment

best response confirmed by Norman_Glenn (Brass Contributor)
Solution

@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.

Excellent, just as I wanted thank you.
1 best response

Accepted Solutions
best response confirmed by Norman_Glenn (Brass Contributor)
Solution

@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.

View solution in original post