SOLVED

Formula Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3074374%22%20slang%3D%22en-US%22%3EFormula%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074374%22%20slang%3D%22en-US%22%3EI%20have%20a%20formula%20that%20can%20spot%20a%20word%20in%20a%20Cell%20and%20add%20a%20letter%20to%20a%20text%20in%20another%20cell.%20ie%20if%20cell%20A3%20shows%20WT%20and%20cell%20B3%20shows%20Card%20then%20change%20A3%20to%20WTC....%20What%20I'm%20need%20to%20do%20now%20is%20have%20a%20formula%20that%20can%20spot%20an%20other%20word%20that%20is%20%22Bad%22%20in%20B3%20and%20again%20change%20cell%20A3%20to%20WTCB.%20So%20it's%20finding%20two%20Words%20in%20one%20cell...%20iv%20attached%20a%20formula%20that%20I'm%20using.%20Just%20need%20to%20know%20how%20to%20search%20and%20edit%20for%20a%202nd%20word%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074374%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3074540%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074540%22%20slang%3D%22en-US%22%3EI%20don't%20see%20any%20text%20manipulation%20in%20that%20formula.%20i%20see%20you%20subtracting%202%20numbers%20based%20on%20INDEX()%20of%202%20arrays.%20That%20said%2C%20you%20are%20looking%20up%20if%20%24A20%3D...%20and%20then%20INDEX%20based%20on%20the%20location%20of%20%24A20%20in%20the%20array%20%24A%2420%3A%24A%2460.%20I%20get%20that%20you%20are%20probably%20copying%20down%20so%20%24A20%20increments%20down%20%24A21%2C%20%24A22%2C%20etc...%20but%20why%20do%20you%20need%20the%20MATCH%20part%3F%20Can't%20you%20just%20use%20%24W20%20and%20%24S20%20instead%20of%20those%20INDEX(MATCH())%3F%20Instead%20of%20just%20the%20MATCH%20condition%20you%20could%20use%20ROW(%24A1)%20and%20as%20that%20copies%20down%20it%20will%20increment%20accordingly%20to%20give%20you%201%2C2%2C3....%20or%20as%20a%20more%20generic%20solution%20(that%20allows%20you%20to%20copy%20it%20to%20rows%20%26lt%3B%2020%20you%20can%20use%20ROW(%24A20)-ROW(%24A%2419)%20and%20if%20you%20HAVE%20to%20copy%20down%20to%20row%201%20you%20can%20use%20ROW(%24A20)%20-%20ROW(%24A%2420)%20%2B%201.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3079312%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3079312%22%20slang%3D%22en-US%22%3EHi%20mtarler..%20I'm%20using%20that%20fourmla%20to%20look%20down%20column%20B%20and%20see%20if%20any%20cell%20says%20cardboard%20and%20if%20it%20does%20then%20in%20column%20A%20Use%20the%20text%20from%20column%20B%20but%20put%20C%20at%20end%20to%20show%20its%20card.%20What%20I%20need%20to%20add%20to%20that%20fourmla%20is%20to%20do%20as%20it%20is%20but%20also%20look%20down%20column%20C%20and%20if%20it%20any%20cells%20say%20good%20then%20place%20a%20G%20at%20end%20of%20text%20in%20column%20A..%20iv%20attached%20below%20what%20I'm%20doing%20so%20far..%3C%2FLINGO-BODY%3E
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 (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.