IF and Right Functions
in [Functions]
|
Prev: Sum Function Visible Cells only
Next: Vlookup issue
From: Bernard Liengme on 20 Apr 2010 12:19 Elaine, I am trying a new way to reply to questions (where appropriate). I have made a file that takes Mike's formula apart step-by-step starting from the inside. This will help you see how it works. The file is housed at http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/TextExtract.xls This is the Microsoft Skydrive locale where one can store private and public files Just copy the URL into your browser I would be interested in your reaction to getting answers like this best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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: Mike H on 20 Apr 2010 12:31 Glad i could help > Can you give me a break-down of the formula you gave? Of course. First, to summarise it relies on not finding the string "^^" in the string which is a reasonably safe bet. Simplified and for your first string the formula evaluates to this and I think that requires no further explanation except how do we get the number 8 which is the position of the character of the string you want. 1024 is just the number of characters to rteurn to ensure we get them all =MID(A1,8,1024) This part of the formula if put in a cell on its own returns the 8 =FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"-",""))))+1 But How? the formula substitues every instance of - with ^^ and remember that's the gamble. If there's already an instance of ^^ where in a mess but that's unlikely This bit SUBSTITUTE(A1,"-","^^") creates a temporary new string that looks like this 30^^179^^TC1 This bit tells it to find ^^ in this new string =FIND("^^", but we need to know which instance to find (we want the second) this bit tests the length of the string with - substituted with nothing "" compared to the length of the original string in A1 LEN(""&A1)-LEN(SUBSTITUTE(A1,"-","")) from this we now know there are 2 instances of - in the full string so we tell the formula to look for the second instance of ^^ which returns 7 add 1 and we have our number 8 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "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 > > >
From: Mike H on 20 Apr 2010 13:19 Bernard, > I would be interested in your reaction to getting answers like this It's a darn sight clearer than the explanation I gave to how my formula worked -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Bernard Liengme" wrote: > Elaine, > I am trying a new way to reply to questions (where appropriate). > I have made a file that takes Mike's formula apart step-by-step starting > from the inside. > This will help you see how it works. > The file is housed at > http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/TextExtract.xls > This is the Microsoft Skydrive locale where one can store private and public > files > Just copy the URL into your browser > I would be interested in your reaction to getting answers like this > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > > "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: John on 20 Apr 2010 14:22 Hi Bernard To receive answers like that would be great, a treasure house to keep for reference. It would be much more work for you!!! Regards John "Bernard Liengme" <bliengme(a)TRUENORTH.stfx.ca> wrote in message news:uzha3UK4KHA.5212(a)TK2MSFTNGP04.phx.gbl... > Elaine, > I am trying a new way to reply to questions (where appropriate). > I have made a file that takes Mike's formula apart step-by-step starting from > the inside. > This will help you see how it works. > The file is housed at > http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/TextExtract.xls > This is the Microsoft Skydrive locale where one can store private and public > files > Just copy the URL into your browser > I would be interested in your reaction to getting answers like this > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > > "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: Bernard Liengme on 21 Apr 2010 16:24
Thanks for comment. Not much work since in many cases I actually make a workbook to ensure I have no typo errors in formulas cheers Bernard "John" <johnd(a)newlook.com> wrote in message news:u$Sq#ZL4KHA.1624(a)TK2MSFTNGP06.phx.gbl... > Hi Bernard > To receive answers like that would be great, a treasure house to keep for > reference. > It would be much more work for you!!! > Regards > John > "Bernard Liengme" <bliengme(a)TRUENORTH.stfx.ca> wrote in message > news:uzha3UK4KHA.5212(a)TK2MSFTNGP04.phx.gbl... >> Elaine, >> I am trying a new way to reply to questions (where appropriate). >> I have made a file that takes Mike's formula apart step-by-step starting >> from the inside. >> This will help you see how it works. >> The file is housed at >> http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/TextExtract.xls >> This is the Microsoft Skydrive locale where one can store private and >> public files >> Just copy the URL into your browser >> I would be interested in your reaction to getting answers like this >> best wishes >> -- >> Bernard Liengme >> Microsoft Excel MVP >> http://people.stfx.ca/bliengme >> >> "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 >>>> > > |