In the following example there is a column of stock codes. The numbers represent our Supplier and the letters after the hyphen (-) indicates our Item number.
Suppose we need to extract the Supplier and Item codes and place in separate cells. If our Supplier codes were all equal length e.g. 3 numbers long, we could simply use the LEFT function.
In the Excel 2010 Training Courses here in London this is a common question.
Example:
Result:
The problem we have is that not all Supplier codes are 3 numbers in length.
We can correct this by using another Excel function word, FIND.
FIND function can return a number value of where a particular character appears, e.g. if we have a word “BEST-STL TRAINING” the FIND function would tell us that the hyphen is the 5th character.
We could use this inside (nested) the LEFT function – But remember we do not want to include the hyphen (-) in our final result so we need to subtract 1.
Example:
NOTE: Without subtracting 1 in the above example, it would return a value of 4
Final Code: =LEFT(A2,FIND(“-“,A2)-1)
We could repeat this for the Item code by using a combination of RIGHT, LEN and FIND nested.
