IF and Right Functions
in [Functions]
|
Prev: Sum Function Visible Cells only
Next: Vlookup issue
From: Elaine on 20 Apr 2010 10:33 Hello, I have part #'s that end in 3 or 4 characters, some with a space. I would like to display the last 3 or 4 characters in another column. I tried using the IF and Right functions and have not solved it. I thank you in advanced for your help on this formula. Example: Part # New Column (Result) 30-179-TC1 TC1 30-181-T C2 T C2 30-185-TC23 TC23
From: Mike H on 20 Apr 2010 10:41 Hi, Try this for a string in a1 and drag down =MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Elaine" wrote: > Hello, > > I have part #'s that end in 3 or 4 characters, some with a space. I would > like to display the last 3 or 4 characters in another column. I tried using > the IF and Right functions and have not solved it. I thank you in advanced > for your help on this formula. > > Example: > Part # New Column (Result) > 30-179-TC1 TC1 > 30-181-T C2 T C2 > 30-185-TC23 TC23 >
From: Elaine on 20 Apr 2010 11:03 WOW! Thank you for your quick response to my question. It works like a champ. Can you give me a break-down of the formula you gave? Thank you, thank you, thank you! Elaine "Mike H" wrote: > Hi, > > Try this for a string in a1 and drag down > > =MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Elaine" wrote: > > > Hello, > > > > I have part #'s that end in 3 or 4 characters, some with a space. I would > > like to display the last 3 or 4 characters in another column. I tried using > > the IF and Right functions and have not solved it. I thank you in advanced > > for your help on this formula. > > > > Example: > > Part # New Column (Result) > > 30-179-TC1 TC1 > > 30-181-T C2 T C2 > > 30-185-TC23 TC23 > >
From: RagDyeR on 20 Apr 2010 12:06 If the examples you gave are true for the entire dataset, you might try these: =MID(A2,8,10) =MID(A2,SEARCH("-",A2,7)+1,10) If your data is not as simple as your examples, then of course, Mike's formula will work for the more complex part numbers. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Elaine" <Elaine(a)discussions.microsoft.com> wrote in message news:ED069296-CABA-4FA7-AABB-FF9E0BC25821(a)microsoft.com... WOW! Thank you for your quick response to my question. It works like a champ. Can you give me a break-down of the formula you gave? Thank you, thank you, thank you! Elaine "Mike H" wrote: > Hi, > > Try this for a string in a1 and drag down > > =MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Elaine" wrote: > > > Hello, > > > > I have part #'s that end in 3 or 4 characters, some with a space. I > > would > > like to display the last 3 or 4 characters in another column. I tried > > using > > the IF and Right functions and have not solved it. I thank you in > > advanced > > for your help on this formula. > > > > Example: > > Part # New Column (Result) > > 30-179-TC1 TC1 > > 30-181-T C2 T C2 > > 30-185-TC23 TC23 > >
From: Jim Thomlinson on 20 Apr 2010 12:08
This section is used to find out how many dashes you have LEN(""&A1)-LEN(SUBSTITUTE(A1,"-","") The total length - the length with the dashes removed. Now that we know how many dashes we have we can use substiture to change the last dash into some other characters that will be unique to the text. SUBSTITUTE(A1,"-","^^", 'number of dashes (last dash) So now we have a uniqe character string just before the ending suffix. We can use the find function to determine where the unique text is FIND("^^", Which tells us how may characters in to find the suffix. Now we just use mid to grab the characters from where the last das was until the end. The 1024 just signifies that we will grab up to character 1024. -- HTH... Jim Thomlinson "Elaine" wrote: > WOW! Thank you for your quick response to my question. It works like a champ. > Can you give me a break-down of the formula you gave? > > Thank you, thank you, thank you! > > Elaine > > "Mike H" wrote: > > > Hi, > > > > Try this for a string in a1 and drag down > > > > =MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,1024) > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "Elaine" wrote: > > > > > Hello, > > > > > > I have part #'s that end in 3 or 4 characters, some with a space. I would > > > like to display the last 3 or 4 characters in another column. I tried using > > > the IF and Right functions and have not solved it. I thank you in advanced > > > for your help on this formula. > > > > > > Example: > > > Part # New Column (Result) > > > 30-179-TC1 TC1 > > > 30-181-T C2 T C2 > > > 30-185-TC23 TC23 > > > |