Microsoft Office Tutorials and References
In Depth Information
MID returns a specific number of characters from a text string, starting
at the position specified, based on the number of characters specified. This
function takes the following arguments:
text — This is the text string that contains the characters you want
start_num — This is the position of the first character you want to
extract in text. The first character in text has start_num 1, and so on.
If start_numis greater than the length of text, MID returns "" (that
is, empty text). If start_numis less than the length of text, but
start_numplus num_charsexceeds the length of text, MID returns the
characters up to the end of text. If start_numis less than 1, MID re-
turns a #VALUE! error.
num_chars — This specifies the number of characters you want MID
to return from text. If num_charsis negative, MID returns a #VALUE!
In Figure 11.58 , it is easy to extract the three-digit vendor code by using
=LEFT(A2,3). It is a bit more difficult to extract the part number. As you
scan through the values in column A, it is clear that the vendor code is con-
sistently three letters. With the dash in the fourth character of the text, it
means that the part number starts in the fifth position. If you are using MID,
you therefore use 5 as the start_numargument.
However, there are a few thousand part numbers in the data set. Right up
front, in cell A4, is a part number that breaks the rule. LUK-04-158 contains
six characters after the first dash. This might seem to be an isolated in-
cident, but in row 10, BWW-BC42TW also contains six characters after the
dash. Because this type of thing happens in real life, two errors in the first
nine records are enough to warrant a little extra attention. The four pos-
sible strategies for extracting the part number are listed in G2:G6. They are
• Ask MID to start at the fifth character and return a large enough
number of characters to handle any possible length (that is,
• Ask MID to start at the fifth character but use TRIM around the
whole function to prevent any trailing spaces from being included
(that is, =TRIM(MID(A2,5,100))).