Skip to main content

Extracting Portions of Text

Extract specific characters or substrings from text using LEFT, RIGHT, and MID formulas.

Overview​

Text extraction formulas let you pull out specific portions of a text string based on position and length. This is essential for parsing structured text, extracting codes, splitting data, and processing formatted strings.

Relevant Formulas​

LEFT​

Returns characters from the start of a text string.

View LEFT Formula Reference →

Returns characters from the end of a text string.

View RIGHT Formula Reference →

MID​

Returns specific characters from the middle of a text string.

View MID Formula Reference →

Basic Usage​

LEFT - Extract from Beginning​

// Get first 3 characters
=LEFT([product_code], 3)
Input: "ABC-12345"
Result: "ABC"

// Get area code from phone
=LEFT([phone], 3)
Input: "555-1234"
Result: "555"

RIGHT - Extract from End​

// Get last 4 characters
=RIGHT([account_number], 4)
Input: "ACCT-9876"
Result: "9876"

// Get file extension
=RIGHT([filename], 3)
Input: "report.pdf"
Result: "pdf"

MID - Extract from Middle​

// Extract characters 5-7 (starting at position 5, length 3)
=MID([sku], 5, 3)
Input: "PRD-CAT-001"
Result: "CAT"

// Extract month from date string "YYYY-MM-DD"
=MID([date_string], 6, 2)
Input: "2024-03-15"
Result: "03"

Common Use Cases​

Parsing Codes and IDs​

// Extract category from SKU "CAT-PROD-VAR"
=LEFT([sku], 3)  // "CAT"

// Extract product code
=MID([sku], 5, 4)  // "PROD"

// Extract variant
=RIGHT([sku], 3)  // "VAR"

Phone Number Formatting​

// Extract area code
=LEFT([phone], 3)

// Extract exchange
=MID([phone], 4, 3)

// Extract line number
=RIGHT([phone], 4)

Date Part Extraction​

// From "YYYY-MM-DD" format
=LEFT([date_str], 4)   // Year: "2024"
=MID([date_str], 6, 2)  // Month: "03"
=RIGHT([date_str], 2)   // Day: "15"

Email Processing​

// Get username (before @)
=LEFT([email], FIND("@", [email]) - 1)

// Get domain (after @)
=MID([email], FIND("@", [email]) + 1, LEN([email]))

Advanced Techniques​

Dynamic Length Extraction​

// Extract everything before a delimiter
=LEFT([text], FIND("-", [text]) - 1)

// Extract everything after a delimiter
=RIGHT([text], LEN([text]) - FIND("-", [text]))

Combining Extraction Functions​

// Extract middle portion between two delimiters
=MID([text],
     FIND("-", [text]) + 1,
     FIND("/", [text]) - FIND("-", [text]) - 1)

Conditional Extraction​

// Extract different parts based on format
=IF(FIND("-", [code]) > 0,
    LEFT([code], FIND("-", [code]) - 1),
    [code])

Working with Variable-Length Text​

Using FIND/SEARCH​

// Extract text before first space
=LEFT([full_name], FIND(" ", [full_name]) - 1)

// Extract text after last space (last name)
=RIGHT([full_name], LEN([full_name]) - FIND(" ", [full_name]))

Using LEN for Dynamic Calculation​

// Get last N characters where N varies
=RIGHT([text], [extract_length])

// Get all but first N characters
=RIGHT([text], LEN([text]) - [skip_chars])

Handling Edge Cases​

NULL Values​

// Safe extraction with NULL check
=IF(ISBLANK([text]), NULL, LEFT([text], 3))

Too-Short Strings​

// Avoid errors when string is shorter than requested length
=IF(LEN([text]) >= 3, LEFT([text], 3), [text])

Missing Delimiters​

// Handle cases where delimiter doesn't exist
=IF(FIND("-", [text]) > 0,
    LEFT([text], FIND("-", [text]) - 1),
    [text])

Extraction Patterns​

Fixed-Position Data​

// Postal code from address: "City, ST 12345"
=RIGHT([address], 5)

// State code
=MID([address], LEN([address]) - 7, 2)

Delimiter-Based Extraction​

// First part: before delimiter
=LEFT([data], FIND("|", [data]) - 1)

// Second part: after delimiter
=MID([data], FIND("|", [data]) + 1, LEN([data]))

Step-by-Step: Creating Extraction Formulas​

  1. Identify Pattern - Determine the position or delimiter pattern
  2. Choose Function - LEFT for start, RIGHT for end, MID for middle
  3. Calculate Position - Use FIND/SEARCH if delimiter-based
  4. Handle Edge Cases - Add NULL checks and validations
  5. Test - Verify with various input lengths
  6. Document - Comment the extraction logic

Best Practices​

  1. Validate Input - Check for NULL and unexpected formats
  2. Use FIND Carefully - FIND returns error if text not found
  3. Consider Trimming - Use TRIM to remove whitespace
  4. Test Edge Cases - Empty strings, single characters, missing delimiters
  5. Document Assumptions - Note expected text format

Performance Considerations​

  • Index Source Columns - If extracting from indexed columns
  • Avoid Complex Nesting - Break into multiple columns if very complex
  • Pre-validate Data - Clean data before extraction when possible

Additional Formula References​

Additional examples and screenshots will be added soon.