Formula Excel : Extract Parts of a Text String

One of the most important techniques for manipulating text in Excel is the capability to extract specific portions of text. Using Excel’s LEFT, RIGHT, and MID functions, you can perform tasks such as:

• 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

Image 3-4 demonstrates how using the LEFT, RIGHT, and MID functions can help easily accomplish these tasks.

Image 3-4: Using the LEFT, RIGHT, and MID functions.

How it works

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 string you are evaluating and the number of characters you need extracted from the left of the text string. In the example, you extract 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 extracted from the right of the text string. In the example, you extract 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 extracting; and the number of characters you need extracted. In the example, you start at the fourth character in the text string and extract one character.


=MID(A14,4,1)

Categories: Formula