Microsoft Office Tutorials and References
In Depth Information
LEFT(), MID(), and RIGHT(): Copying Portions of a Text String
Manipulating Text
LEFT(), MID(), and RIGHT(): Copying Portions of a Text String
Just as you can join pieces of text, you can also split up a string of text. The LEFT(),
MID(), and RIGHT() functions let you extract a portion from a larger text string.
For example, the LEFT() function takes two arguments: the text you want to
examine, and the number of characters that Excel should extract, starting from the string’s
left side:
LEFT(text, num_characters)
To take the first four letters from the text in cell A1, you’d use the formula:
=LEFT(A1, 4)
Assuming the cell contains the text tofurkey , the result of this formula would be the
shortened text tofu .
The RIGHT() function performs the same operation, but it extracts letters starting
from the right side of a string. For example, consider the following formula:
=RIGHT(A1, 5)
If you use this function with the same string, you’ll simply end up with the text urkey .
The MID() function is more powerful than the LEFT() and RIGHT() functions, as
it has the ability to extract a consecutive series of characters from anywhere inside a
string. When using the MID() function, you need to supply three arguments: the text
you’re evaluating, the starting position, and the number of characters. Excel
numbers each letter in a string (it also counts spaces), starting with 1 for the first letter,
2 for the second letter, and so on. That means if you specify a starting position of 3
and a length of 2, Excel extracts the third and fourth characters. The basic formula
looks like this:
MID(text, start_position, number_of_characters)
Here’s an example that copies characters from the middle of a string. If the cell A1
contains the text Swanky Franks , the following formula returns the value Frank.
=MID(A1, 8, 5)
Note: LEFT(), MID(), and RIGHT() all pluck out the strings you specify, but they leave the original
contents of the cell you’re examining unchanged.
TRIM() and CLEAN(): Removing Unwanted Spaces and
Non-Printing Characters
The TRIM() and CLEAN() functions perform minor cleanup on any strings of text
you run through them. TRIM() removes any leading and trailing spaces; it also
changes any series of more than one space to a single space. Thus, if you use TRIM()
on the text string “ Hello There “ the altered text becomes “Hello There.” TRIM() can
be quite handy for fixing erratic spacing.

Search JabSto ::

Custom Search