 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Another Text Function problem
In case RD, Ken, or Barbara did not get my message, thanks for your help with the last text function problem. Your examples and explanations were very helpful! Here's another problem. Here is a list of imported codes: JMCC2C KAO2D JMCC2A JMCC2E RPT2A RPT2B RPT2C RPT2E PEC1C PEC1D The first few characters represent the description, the number (second from the end) represent a region, and the last number represents the manufacturer. The manufacturer column is the easy one. But I am trying to figure out how to extract the description code (which can vary between 1 to 4 characters, and the region code. =LEFT(A1,LEN(A1)-2) =MID(A1,LEN(A1)-1,1) > In case RD, Ken, or Barbara did not get my message, thanks for your help > with the last text function problem. Your examples and explanations were [quoted text clipped - 18 lines] > figure out how to extract the description code (which can vary between 1 to > 4 characters, and the region code. Hi Bob, Thanks for your response. Both the solutions you provided worked, but I am not sure why the second one works: =MID(A1,LEN(A1)-1,1) If the reference cell A1 contains COOP1C, I would think your formula would return a "C" not "1", which is the correct result. Here's my thinking (please tell me why I am wrong): The first argument, A1, references COOP1C. The second argument, LEN(A1)-1, would return COOP1, which is the word from which I want to extract my character. The third argument is 1, the number of characters I want returned. But doesn't Excel evaluate the second argument, COOP1, left to right? So, wouldn't it return a "C" instead of a "1"? I understand that it is returning the correct answer, but I don't know why. > =LEFT(A1,LEN(A1)-2) > [quoted text clipped - 24 lines] > to >> 4 characters, and the region code. > In case RD, Ken, or Barbara did not get my message, thanks for your help > with the last text function problem. Your examples and explanations were [quoted text clipped - 18 lines] > figure out how to extract the description code (which can vary between 1 > to 4 characters, and the region code. Tom Description code: =LEFT(A1,LEN(A1)-2) Region code: =MID(A1,LEN(A1)-1,1)  SignatureBest regards Leo Heuser Followup to newsgroup only please. Hi Leo, Thanks for your response. I put the following question to Bob also, since he provided the same solution. Both the solutions you provided worked, but I am not sure why the second one works: =MID(A1,LEN(A1)-1,1) If the reference cell A1 contains COOP1C, I would think your formula would return a "C" not "1", which is the correct result. Here's my thinking (please tell me why I am wrong): The first argument, A1, references COOP1C. The second argument, LEN(A1)-1, would return COOP1, which is the word from which I want to extract my character. The third argument is 1, the number of characters I want returned. But doesn't Excel evaluate the second argument, COOP1, left to right? So, wouldn't it return a "C" instead of a "1"? I understand that it is returning the correct answer, but I don't know why. Thanks! Tom >> In case RD, Ken, or Barbara did not get my message, thanks for your help >> with the last text function problem. Your examples and explanations were [quoted text clipped - 28 lines] > > =MID(A1,LEN(A1)-1,1) > Hi Leo, > [quoted text clipped - 26 lines] > > Tom Hi Tom You're welcome. LEN(A1) returns the *length* of the string in A1, so LEN(A1)-1 is 6-1 or 5. =MID(A1,LEN(A1)-1,1) starts at character 5, and picks one character, which amounts to 1. Leo Heuser For the description, try this: =LEFT(A1,LEN(A1)-2) And the region: =LEFT(RIGHT(A1,2)) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > In case RD, Ken, or Barbara did not get my message, thanks for your help > with the last text function problem. Your examples and explanations were [quoted text clipped - 18 lines] > figure out how to extract the description code (which can vary between 1 > to 4 characters, and the region code. Hi RD, I must say, that second example of yours (the one that returns the reason) is interesting. I had to stare at it for a few minutes until I figured it out. Thanks very much! Tom > For the description, try this: > [quoted text clipped - 26 lines] >> figure out how to extract the description code (which can vary between 1 >> to 4 characters, and the region code. In XL, there's always more then 1 way to skin a cat. Appreciate the feed-back. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- Hi RD, I must say, that second example of yours (the one that returns the reason) is interesting. I had to stare at it for a few minutes until I figured it out. Thanks very much! Tom > For the description, try this: > [quoted text clipped - 26 lines] >> figure out how to extract the description code (which can vary between 1 >> to 4 characters, and the region code. Thanks again to Bob, Leo, and RD. I have not used a lot of text functions in the past and I need to get a better understanding how the LEN function works with the other text functions. Your examples have given me the start I needed. Tom > In case RD, Ken, or Barbara did not get my message, thanks for your help > with the last text function problem. Your examples and explanations were [quoted text clipped - 18 lines] > figure out how to extract the description code (which can vary between 1 > to 4 characters, and the region code.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.