Excel LEFT, RIGHT, MID

LEFT RIGHT MID Basics

• LEFT(text, [num_chars]): Returns the specified number of characters from the start (left) of a text string.
• RIGHT(text, [num_chars]): Returns the specified number of characters from the end (right) of a text string.
• MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at the position you specify.

Phone Numbers

LEFT/MID: Extracting Area Codes

Suppose we have a list of phone numbers in column A. Here’s how to extract the area code:

=LEFT(A2, 3)

This formula assumes that the area code is the first three digits of the phone number. If the phone number is formatted with parentheses, like (123) 456-7890, you might need to adjust for this:

=MID(A2, 2, 3)

This formula skips the first character (the opening parenthesis) and then takes the next three characters as the area code.

RIGHT: Extracting Last 4 digits

Similarly, extracting the last four digits of a phone number, which is often referred to as the line number, can be done using the RIGHT() function. Example:

For a phone number in the format 123-456-7890:

=RIGHT(A2, 4)

Normalize First

If you just want to process numbers, and the formats of them are varied, you can make a normalization first, then process that column

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), ".", ""), " ", ""), "-", "")

Then in Column C, you could parse the number using LEFT, MID, RIGHT

Parsing IPv4

Sometimes you need to get specific information from an IP and it’s useful to understand the octet divisions. One example is to assign binary numbers to network CIDR. In order to do that you need to know what each octet is so you can run the math on that octect and add them together.

Example Data

Formulas

This uses a combination of SUBSTITUTE, LEFT, MID, RIGHT to extract each octect into it’s own cell.