Harold, of Phoenix asks, "I use Excel for serial numbers on products listed in our inventory. The product numbers have 12 digits and there are certain numbers in the middle of the serial number which tell us the product belongs in a certain category. Is there an easy way to find these numbers in Excel?"
This Excel tip shows you an easy formula that allows for such as search.
Let's start with an example as mentioned above.
Harold has a list of inventory numbers that are 12 digits in length.
We'll use numbers such as 123-345-567-888. These numbers are listed in Column B of our example. See image above and left.
If the 2nd set of 3 digits represents the category of item, we can identify categories by using the =MID function in Excel.
To use the =MID function in Excel, follow these steps.
Place the word "Category" in cell C1 to represent the header.
Place the following formula in cell C2.
=MID(B2,5,3)
"B2" represents the Serial Number in Column B.
"5" represents the starting character number. In this example, the hyphen is a character in the Serial Number field. If Column B did not include the hyphens and the formatting added the hyphens, then this number would have been "4."
"3" represents the number of characters to compare with the starting character as one (1).
Copy the formula to all rows containing data.
The result of the above formula for the serial number 123-345-567-888 is "345."
Continuing to help anyone reading the results, users could add a Column D explaining the results.
Column D might have use a =VLOOKUP formula.
This Excel tip "MID" function can help anyone search for a certain set of characters within a larger set of characters in a field.
--------------------------------------------------
To receive notifications on all of my articles, click the "Subscribe" button at the top of this page.
As usual, if you have questions or ideas for articles, "Go Ask Debbie!"
Also, be sure to follow me on Facebook.
-------------------------------------------------
Comments