Microsoft Office Tutorials and References
In Depth Information
Using LEFT, MID, or RIGHT to Split Text
If you ’ ve ever created a small web page, you might have learned that
browsers ignore consecutive spaces. If you really want to keep two words
separated by four spaces, you need to use
Word1 Word2. I learned this trick somewhere on
the Web and never really thought about what means. It turns out that
it is a nonbreaking space. And, you guessed it, a nonbreaking space occupies
character position 160, so it looks just like a space. Web designers use it all
the time to format web pages. Consequently, it is ending up in data that people
paste into Excel from the Web, and it is making it appear that TRIM does not
LEFT ,, MID
MID , or
, or RIGHT
RIGHT to Split Text
to Split Text
One of the newer rules in information processing is that each field in a data-
base should contain exactly one piece of information. Throughout the his-
tory of computers, there have been millions of examples of people trying
to cram many pieces of information into a single field. Although this works
great for humans, it is pretty difficult to have Excel sort a column by
everything in the second half of a cell.
Column A in Figure 11.58 contains part numbers. As you might guess, the Part
Number field contains two pieces of information: a three-character vendor
code, a dash, and a five-digit part number.
Figure 11.58. LEFT
makes quick work of extracting the vendor code. Several
LEFT makes quick work of extracting the vendor code. Several
varieties of MID
MID oor RIGHT
RIGHT extract the part number.
extract the part number.