Discussion Re: Formula Excel in Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3079317#M131800
<P>Here is the attachment</P>Fri, 28 Jan 2022 11:50:54 GMTNorman_Glenn2022-01-28T11:50:54ZFormula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3074374#M131721
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 W17Thu, 27 Jan 2022 20:00:23 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3074374#M131721Norman_Glenn2022-01-27T20:00:23ZRe: Formula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3074540#M131748
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.Thu, 27 Jan 2022 21:45:28 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3074540#M131748mtarler2022-01-27T21:45:28ZRe: Formula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3079312#M131799
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..Fri, 28 Jan 2022 11:49:00 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3079312#M131799Norman_Glenn2022-01-28T11:49:00ZRe: Formula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3079317#M131800
<P>Here is the attachment</P>Fri, 28 Jan 2022 11:50:54 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3079317#M131800Norman_Glenn2022-01-28T11:50:54ZRe: Formula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3081124#M131833
<P><LI-USER uid="635882"></LI-USER> 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":</P><LI-CODE lang="excel-formula">=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","")</LI-CODE><P>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:</P><P> </P><P> </P><LI-CODE lang="applescript">=B19&IF((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS"),
IF(ISNUMBER(SEARCH("CARDBOARD",C19)),"C","")
&IF(ISNUMBER(SEARCH("GOOD",C19)),"G","")
,"")</LI-CODE><P> </P><P> </P><P>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.</P>Fri, 28 Jan 2022 16:40:18 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3081124#M131833mtarler2022-01-28T16:40:18ZRe: Formula Excel
https://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3082820#M131871
Excellent, just as I wanted thank you.Fri, 28 Jan 2022 20:27:34 GMThttps://techcommunity.microsoft.com/t5/excel/formula-excel/m-p/3082820#M131871Norman_Glenn2022-01-28T20:27:34Z