If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF and Right Functions
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 |
#2
|
|||
|
|||
IF and Right Functions
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 |
#3
|
|||
|
|||
IF and Right Functions
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 |
#4
|
|||
|
|||
IF and Right Functions
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 |
#5
|
|||
|
|||
IF and Right Functions
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...extExtract.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" wrote in message ... 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 |
#6
|
|||
|
|||
IF and Right Functions
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 |
#7
|
|||
|
|||
IF and Right Functions
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...extExtract.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" wrote in message ... 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 . |
#8
|
|||
|
|||
IF and Right Functions
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" wrote in message ... 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...extExtract.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" wrote in message ... 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 |
#9
|
|||
|
|||
IF and Right Functions
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" wrote in message ... 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" wrote in message ... 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...extExtract.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" wrote in message ... 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 |
#10
|
|||
|
|||
IF and Right Functions
Mike, Jim, RagDyeR, and John,
All of you are GREAT! I thank each and one of you for your input. I'm so glad I have "background" support. Again, my many thanks for all of your efforts and continued support. Respectfully, Elaine "Bernard Liengme" wrote: 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" wrote in message ... 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" wrote in message ... 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...extExtract.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" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|