Key Take-Aways

- LEFT returns a specified number of characters starting from the left of a text string.

- RIGHT returns a specified number of characters starting from the right of a text string.

- MID extracts characters from the middle of a string using a start position and length.

- LEN returns the total count of characters in a text string and is useful for validation.

- Combining these functions lets you split structured text like phone numbers into area code, prefix, and line number.

## What these functions do and when to use them

If you need to pull parts of text out of a larger string, Excel has four simple functions that will do the job:

- LEFT(text, num_chars): returns num_chars starting from the left.

- RIGHT(text, num_chars): returns num_chars starting from the right.

- MID(text, start_num, num_chars): returns num_chars starting at start_num counting from the left, with the first character at position 1.

- LEN(text): returns the total number of characters in the text string.

These are ideal when the text you want to extract appears in consistent positions. A common case is phone numbers stored as text with a consistent format.

## Syntax and quick examples

- LEFT: `=LEFT(A2, 3)` returns the first three characters from the left of A2.

- RIGHT: `=RIGHT(A2, 4)` returns the last four characters from the right of A2.

- MID: `=MID(A2, 5, 3)` starts at position 5 and returns three characters.

- LEN: `=LEN(A2)` returns how many characters are in A2.

Note: MID counts positions starting at 1. If you give MID a start position beyond the length of the string, it returns an empty string.

## Step-by-step example: splitting a phone number column

Scenario: you have phone numbers in column N (as text) and you want to split them into area code, prefix, and line number.

1. Add three new columns:

- Column O: Area Code

- Column P: Prefix

- Column Q: Line Number

2. Decide the definitions:

- Area code: first three characters

- Prefix: the next three characters after the area code

- Line number: last four characters

3. Formulas (assume the first phone number is in N2):

- Area code (first three characters):

`=LEFT(N2, 3)`

- Prefix (three characters from the middle, starting at position 5):

`=MID(N2, 5, 3)`

Explanation: count 1, 2, 3, 4, 5 to find the start position, then return three characters.

- Line number (last four characters):

`=RIGHT(N2, 4)`

- Optional: check length with LEN:

`=LEN(N2)` ; for a standard 10-digit phone number stored without separators this should return 10.

4. Apply the formulas down the column to process all rows.

This approach matches the instructor example: LEFT for area code, MID for the middle chunk, RIGHT for the line number, and LEN to confirm the number of characters.

## Practical variations and handling real-world phone data

Real datasets often contain parentheses, spaces, dashes, or country codes. LEFT, MID, and RIGHT assume consistent positions. If your phone numbers contain separators you may need to normalize them first.

Common normalization steps:

- Use SUBSTITUTE to remove characters: `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2, "(", ""), ")", ""), "-", "")` to remove parentheses and dashes.

- Use TRIM to remove leading or trailing spaces: `=TRIM(N2)`.

- Combine cleaning before extraction: `=LEFT(SUBSTITUTE(TRIM(N2), "-", ""), 3)`

If numbers include a country code, you may need to use LEN to detect length and adjust MID start positions accordingly.

## When LEFT, MID, RIGHT will not work well

- The parts you need are not in fixed positions.

- The text contains different numbers of separators or variable blocks.

In those cases consider using:

- TEXTSPLIT (Excel 365 onwards) to split by delimiter.

- FIND or SEARCH with MID to find the start position dynamically.

- Flash Fill for one-off or manual corrections.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Watch for hidden spaces. Use TRIM to remove extra spaces before extracting.

- Ensure phone numbers are stored as text. If they are numeric, Excel may drop leading zeros. Use `TEXT` or prefix with an apostrophe.

- MID start positions are 1-based. Off-by-one errors are common when counting positions manually.

- Use LEN to validate results. For example `=IF(LEN(N2)=10, "OK", "Check")` quickly flags rows that do not match expected length.

- When you remove formatting characters with SUBSTITUTE, validate that you did not accidentally remove important digits.

- If data varies, use helper columns: first clean, then extract. That makes formulas easier to debug.

## FAQ

### How do I extract an area code if phone numbers include parentheses or dashes?

Clean the number first, for example remove parentheses and dashes with SUBSTITUTE, then apply LEFT. Example: `=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2,"(",""),")",""),"-",""),3)`.

### What if phone numbers have different lengths or country codes?

Use LEN to detect length, or FIND/SEARCH to locate delimiters. You can branch with IF statements to use different extraction logic based on LEN results.

### Can LEFT, MID, and RIGHT handle numbers stored as numeric values?

They work on text. If a cell is numeric, Excel implicitly converts it to text for these functions but you may lose leading zeros. Convert numbers to text first using `TEXT(cell, "0")` or by formatting as text.

### How do I extract a variable length part when delimiters are present?

Use FIND or SEARCH to locate a delimiter and then use MID with the dynamic start position. For example to get text after the first space: `=MID(A2, FIND(" ", A2)+1, LEN(A2))`.

### Why does MID return blank even though I think the start position is correct?

Check for hidden spaces and ensure the start position is within the string length. If start_num is greater than LEN(text) MID returns an empty string.

## Summary of Key Points / Take-Home Messages

- LEFT, MID, and RIGHT are the simplest and most reliable functions for extracting fixed-position text.

- MID requires a start position and a length; remember positions start at 1.

- Use LEN to validate string length before or after extraction.

- Clean data first with TRIM and SUBSTITUTE when separators or extra characters are present.

- If text positions vary, use FIND/SEARCH, TEXTSPLIT, or conditional logic to handle variations.