Extracting parts of a text string –
One of the most important techniques for manipulating text in Excel is the ability to
extract specific portions of text. Using Excel’s LEFT, RIGHT, and MID functions, you can do
things like this:
- Convert nine-digit postal codes into five-digit postal codes
- Extract phone numbers without the area code
- Extract parts of employee or job codes for use somewhere else
Figure below demonstrates how using the LEFT, RIGHT, and MID functions can help easily
accomplish these tasks.
The LEFT function allows you to extract a given number of characters from the left of a
given text string. The LEFT function requires two arguments: the text you are evaluating
and the number of characters you need to be extracted from the left of the text string. In
this example, we are extracting the left five characters from the value in cell A4:
=LEFT(A4,5)The RIGHT function allows you to extract a given number of characters from the right of
a given text string. The RIGHT function requires two arguments: the text string you are
evaluating and the number of characters you need to be extracted from the right of the
text string. In this example, we are extracting the right eight characters from the value in
cell A9:
=RIGHT(A9,8)The MID function allows you to extract a given number of characters from the middle of
a given text string. The MID function requires three arguments: the text string you are
evaluating, the character position in the text string from where to start the extraction,
and the number of characters you need to be extracted. In this example, we are starting at
the fourth character in our text string and extracting one character:
=MID(A14,4,1)