 SOLVED

Contributor

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

# Re: Formula Excel

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.

# Re: Formula Excel

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

# Re: Formula Excel

Here is the attachment

best response confirmed by Norman_Glenn (Contributor)
Solution

# Re: Formula Excel

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

# Re: Formula Excel

Excellent, just as I wanted thank you.