LEFT Function in Excel
Extract text from the beginning of strings with the LEFT function. Learn syntax, examples, and error solutions for Excel and Google Sheets.
Excel
Google SheetsExcel=LEFT(text, [num_chars])Practical Examples
Extract Area Code from Phone Number
Get the first 3 digits from a standard US phone number format
Extract First Name from Full Name
Isolate first name by combining LEFT with FIND to locate the space
Parse Product SKU Components
Extract category code from structured product identifiers
Extract Date from DateTime String
Isolate date portion from timestamp entries
Create Email Username from Full Email
Extract username portion before @ symbol for user management
Extract Currency Symbol from Price
Isolate currency indicators from international price data
Batch Process File Extensions
Extract file name without extension for bulk renaming operations
Common Errors and Solutions
LEFT function returns #VALUE! error
The num_chars parameter is negative or non-numeric. This often occurs when using calculated values that unexpectedly return negative numbers or text.
1. Check if num_chars is negative using =IF(B2<0, 1, B2) 2. Ensure the second parameter is numeric with VALUE() function 3. Use MAX(1, your_calculation) to prevent negative values 4. Wrap in IFERROR for graceful error handling: =IFERROR(LEFT(A1, B1), "")
Always validate the num_chars parameter with data validation rules or use conditional logic to ensure positive numeric values
Example:
LEFT returns numbers as text or incorrect characters
Excel stores numbers as text after LEFT extraction, or hidden characters exist in the source data
1. Use VALUE(LEFT()) to convert result to number 2. Apply TRIM(CLEAN()) to source text first 3. Check for non-breaking spaces with SUBSTITUTE(A1, CHAR(160), "") 4. Use Text to Columns feature for bulk conversion
Clean your data with TRIM and CLEAN functions before applying LEFT, especially with imported data
Example:
Excel doesn't recognize LEFT function
Misspelling of function name, using LEFTB instead of LEFT, or regional settings using different function names
1. Verify spelling: LEFT not LFET or LEFT() 2. Check if using correct function (LEFT vs LEFTB for byte operations) 3. Ensure English function names in non-English Excel versions 4. Check for missing = sign at formula start
Use Formula AutoComplete feature and always start formulas with = sign
Example:
LEFT extracts too many or too few characters
Hidden characters, different encoding (Unicode vs ASCII), or counting visible characters incorrectly
1. Use LEN() to check actual string length 2. Look for hidden characters with =CODE(MID(A1,1,1)) 3. Account for special characters taking multiple bytes 4. Use LENB() for byte-accurate counts in multi-byte character sets
Test with LEN function first and visually inspect data for hidden characters
Example:
Advanced Tips and Best Practices
Combine LEFT with FIND or SEARCH for intelligent text extraction. Instead of hard-coding character counts, use =LEFT(A1, FIND("delimiter", A1) - 1) to extract everything before a specific character or word. This makes your formulas adaptive to varying text lengths.
LEFT is extremely fast, but when combined with volatile functions like INDIRECT or OFFSET, it can slow down large spreadsheets. For datasets over 50,000 rows, consider using Power Query or array formulas for better performance.
Always wrap LEFT in IFERROR when using it with FIND or SEARCH functions. This prevents cascading errors when delimiters are missing: =IFERROR(LEFT(A1, FIND(" ", A1) - 1), A1) returns the full text if no space is found.
For multilingual data with special characters, use LEFTB for byte-precise extraction in double-byte character sets (DBCS) like Chinese, Japanese, or Korean. Regular LEFT counts characters, while LEFTB counts bytes.
In Excel 365 and Google Sheets, LEFT works with dynamic arrays. Use =LEFT(A2:A100, 3) to extract from an entire column at once. This is significantly faster than copying formulas down manually.
Need Help with LEFT Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master CONCATENATE to join text in Excel and Google Sheets. Learn syntax, examples, errors, and modern alternatives like CONCAT and TEXTJOIN.
Excel