Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
I am looking for a formula to place what is in B in either C or D based off
of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
Ostensibly, put the following into C1 and D1, then copy down:
C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
On Wed, 22 Jul 2009 09:52:01 -0700, Jeremy
wrote: I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 Put this formula in cell C1: =IF(NOT(ISERROR(FIND("EX",A1))),B1,"") and this formula in cell D1: =IF(NOT(ISERROR(FIND("ENT",A1))),B1,"") Copy cells C1 and D1 down as far as needed. Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
If EX is always the first 2 or last 2 letters of the code, then this should
work for C: =IF(OR(LEFT(A1,2)="EX",RIGHT(A1,2)="EX")),B1,"") If ENT is always the first 3 letters, then this can be used in D: =IF(LEFT(A1,3)="ENT",B1,"") -- Please remember to indicate when the post is answered so others can benefit from it later. "Jeremy" wrote: I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
=IF(ISERROR(SEARCH($C$1,A2)),"",B2) =IF(ISERROR(SEARCH($D$1,A2)),"",B2) =IF(ISERROR(SEARCH($C$1,A3)),"",B3) =IF(ISERROR(SEARCH($D$1,A3)),"",B3) =IF(ISERROR(SEARCH($C$1,A4)),"",B4) =IF(ISERROR(SEARCH($D$1,A4)),"",B4) =IF(ISERROR(SEARCH($C$1,A5)),"",B5) =IF(ISERROR(SEARCH($D$1,A5)),"",B5) Where C1 = EX and D1 = ENT. Loads of ways to do this - BUT perhaps the best way would be to use Regular Expressions..!! "Jeremy" wrote: I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
PS....
I wrote: if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") If you always expect either EX or ENT -- that is, there is never a case where neither is present -- D1 can be simplified, namely: =if(C1="", B1, "") ----- original message ----- "JoeU2004" wrote in message ... Ostensibly, put the following into C1 and D1, then copy down: C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
Joe,
Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Thanks "JoeU2004" wrote: Ostensibly, put the following into C1 and D1, then copy down: C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
"Jeremy" wrote:
Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Glad it helped. It is difficult to address your follow-up question because I am not sure which set of formulas you opted for, and "have two variables" is a little vague. Perhaps the following paradigm will help: =if(and(D1=E1,isnumber(search("EX",A1))), B1, "") If that does not answer your question (I can understand why it wouldn't), post a specific example, as you did before, showing how you would use the two conditions to make a decision. ----- original message ----- "Jeremy" wrote in message ... Joe, Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Thanks "JoeU2004" wrote: Ostensibly, put the following into C1 and D1, then copy down: C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
=IF(ISNUMBER(SEARCH("IN",$G2)),$L2,"") is the formula that I am using. What
I am trying to do is where the "IN" is have it where it can be "IN" or "ENT". Thank you "JoeU2004" wrote: "Jeremy" wrote: Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Glad it helped. It is difficult to address your follow-up question because I am not sure which set of formulas you opted for, and "have two variables" is a little vague. Perhaps the following paradigm will help: =if(and(D1=E1,isnumber(search("EX",A1))), B1, "") If that does not answer your question (I can understand why it wouldn't), post a specific example, as you did before, showing how you would use the two conditions to make a decision. ----- original message ----- "Jeremy" wrote in message ... Joe, Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Thanks "JoeU2004" wrote: Ostensibly, put the following into C1 and D1, then copy down: C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help needed
=IF(OR(ISNUMBER(SEARCH("IN",$G2)),ISNUMBER(SEARCH( "ENT",$G2))),$L2,"")
-- David Biddulph "Jeremy" wrote in message ... =IF(ISNUMBER(SEARCH("IN",$G2)),$L2,"") is the formula that I am using. What I am trying to do is where the "IN" is have it where it can be "IN" or "ENT". Thank you "JoeU2004" wrote: "Jeremy" wrote: Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Glad it helped. It is difficult to address your follow-up question because I am not sure which set of formulas you opted for, and "have two variables" is a little vague. Perhaps the following paradigm will help: =if(and(D1=E1,isnumber(search("EX",A1))), B1, "") If that does not answer your question (I can understand why it wouldn't), post a specific example, as you did before, showing how you would use the two conditions to make a decision. ----- original message ----- "Jeremy" wrote in message ... Joe, Thank you for your help I believe this is what I am going to use. One more thing on the formula. What if I have two variables for C1 where I want to be EX and EXT? Thanks "JoeU2004" wrote: Ostensibly, put the following into C1 and D1, then copy down: C1: =if(isnumber(search("EX",A1)), B1, "") D1: =if(isnumber(search("ENT",A1)),B1,"") Caveats: 1. SEARCH is case-insensitive; for example, it will match "ex" as well as "EX". If you prefer not to match lowercase, use FIND instead of SEARCH. 2. This will match "EX" or "ENT" contained anywhere, an apparent requirement given your examples. But consider the following ambiguity in A1: ENT EX51B1A1. The formulas above will yield the same value in both C1 and D1. There is no solution for #2 unless you qualify the syntactic requirements. For example, if EX or ENT can only appear in the left or right of the text, and they will never appear in both left and right, then the following might suffice: C1: =if(or(left(A1,2)="ex", right(A1,2)="ex"), B1, "") D1: =if(or(left(A1,3)="ent", right(A1,3)="ent"), B1, "") Again, those formulas are case-insensitive. ----- original message ----- "Jeremy" wrote in message ... I am looking for a formula to place what is in B in either C or D based off of if A has an EX or ENT in it. EX goes in C where ENT goes in D. Below the first example is what I have and the second is what I would like. Thank you A B C D 1 CGVEHEX 1500 2 ENTB2A0 2500 3 EX NM51B1A1 2600 4 ENT W NM 2780 Example 2 A B C D 1 CGVEHEX 1500 1500 2 ENTB2A0 2500 2500 3 EX NM51B1A1 2600 2600 4 ENT W NM 2780 2780 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
IF AND Formula Help Needed | Excel Discussion (Misc queries) | |||
Formula needed - is this possible? | Excel Discussion (Misc queries) | |||
Formula Needed... | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions |