Using LEFT, MID, RIGHT to dissect Excel cells
Q. I have several product numbers that each appear in a single cell in Excel. Each product number contains the product line (the first three digits), the product color (the middle four digits), and the product size (the last six digits). I have to dissect each product number by the three categories. Is there a way to do this quickly?
A. It is very easy to dissect the contents of a cell in Excel using the functions LEFT, MID, and RIGHT. All three functions are easy to learn. I have created a workbook with an example of using LEFT, MID, and RIGHT. Click here to access the workbook and watch the accompanying video at the bottom of the page.
Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
When you want to extract a certain number of characters from the far left of a text string, the function LEFT would be appropriate. The LEFT function contains two arguments: Text and Num_chars. The input for the Text argument is the cell that contains the text string that needs to be dissected. The input for the Num_chars argument is the number of characters that should be extracted from the far left of the text string referenced in the Text argument. See the screenshot below for the LEFT function in this example. Cell A2 is being referenced for the Text argument because it contains the Product Number from which we want to extract information. The number 3 is chosen for Num_chars because we want to extract the three characters from the far left of the text string in cell A2.
When you want to extract a certain number of characters from the middle of a text string, the function MID would be appropriate. The MID function contains three arguments: Text, Start_num, and Num_chars. The input for the Text argument is the cell that contains the text string that needs to be dissected, just as it was for the LEFT function. The input for the Start_num argument is the character at which the extraction in the text string should start. Keep in mind that characters include numbers, letters, spaces, and symbols. The input for the Num_chars argument is the number of characters that should be extracted, beginning with the starting character described in the second argument. See the screenshot below for the MID function in this example.
Cell A2 is referenced for the Text argument because it contains the Product Number from which we want to extract information. The number 5 was chosen for the Start_num because we want to start extracting with the fifth character in the text string in cell A2. Although the “7” in “7282” is the fourth number, it is the fifth character because the dash is also considered a character. The number 4 was chosen for Num_chars because we want to extract four characters (7282), beginning with the starting character described in the second argument.
When you want to extract a certain number of characters from the far right of a text string, the function RIGHT would be appropriate. The RIGHT function contains two arguments: Text and Num_chars. The input for the Text argument is the cell that contains the text string that needs to be dissected, exactly as it was for the LEFT and MID functions. The input for the Num_chars argument is the number of characters that should be extracted from the far right of the text string described in the Text argument. See the screenshot below for the RIGHT function in this example. Cell A2 is referenced for the Text argument because it contains the Product Number from which we want to extract information. The number 6 is chosen for Num_chars because we want to extract six characters from the far right of the text string in cell A2.
Once you have the three formulas in cells B2, C2, and D2, you can select all three cells and drag the formulas down. Or even better, select cells B2, C2, and D2. You will see a green box in the bottom right corner of the selected cells (see the screenshot below). Double–click that box and, as long as your formulas are in columns right next to the text strings, all rows will be populated with the formulas. This is convenient, especially if you have many rows of data.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
Research & References of Using LEFT, MID, RIGHT to dissect Excel cells|A&C Accounting And Tax Services
Source
0 Comments