Description
Use this function to extract a specified number of characters from the start of a text string. Supported in Chains.
This is useful for isolating specific parts of text data, such as area codes from phone numbers or the first few letters of names.
Syntax
LEFT(text, [num_chars])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
text |
Yes | The text string from which you want to extract characters. | A cell reference, a number, a text string, or a formula which results in any of these. |
num_chars |
No | The number of characters you want to extract from the start of the text. | A positive integer, a cell reference containing a positive integer, or a formula which results in either of these. If omitted, defaults to 1. |
Example
Sample data
A |
B |
||
|---|---|---|---|
1 |
1 |
Workiva |
|
2 |
2 |
01 (123) 456-7890 |
|
3 |
3 |
John Doe |
|
4 |
4 |
ABC123 |
|
5 |
5 May 2024 |
4/6/2024 |
|
6 |
eggplant |
The quick brown fox |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Extract a specific number of characters from the start of a text string. | =LEFT(A6, 3) |
Returns the first 3 characters of "eggplant". Result: egg |
| Extract the country code prefix from the start of a phone number. | =LEFT(B2, 2) |
Returns the first 2 characters of "01 (123) 456-7890". Result: 01 |
| Get just the first character of a text string. | =LEFT(B4) |
With no num_chars specified, returns only the first character of "ABC123".Result: A |
| Ask for more characters than a text string actually has. | =LEFT(B1, 10) |
"Workiva" is only 7 characters long, so LEFT returns the whole string instead of erroring out. Result: Workiva |
| Extract characters from the start of a date, using another cell to set how many. | =LEFT(B5, A4) |
Uses the value in A4 (4) as the character count. Since B5 is stored as a date rather than text, LEFT operates on its underlying serial number rather than the displayed "4/6/2024," returning the first 4 digits of that serial value. Result: 4538 |
| Extract a fixed number of characters from a date that's stored as text. | =LEFT(A5, 5) |
Because A5 is text rather than a real date, LEFT reads it literally, returning the first 5 characters of "5 May 2024". Result: 5 May |
| Return everything except the last few characters of a text string. | =LEFT(B1, LEN(B1)-3) |
Calculates the length of "Workiva" minus 3, then returns that many characters from the start — effectively dropping the last 3 characters. Result: Work |
| Extract just the first word from a sentence. | =LEFT(B6, FIND(" ", B6, LEN(B6)-LEN(SUBSTITUTE(B6, " ", "")))-1) |
Locates the relevant space in "The quick brown fox" and returns everything before it. Result: The |
Notes
- If
num_charsis greater than the length of text, LEFT returns the entire text. - If
num_charsis omitted, it defaults to 1, returning only the first character. - If
num_charsis 0, LEFT returns a blank cell. - LEFT treats each character, including spaces and punctuation, as one unit.
- For non-text inputs, LEFT attempts to convert the value to text before processing.
- LEFT can be nested within other functions or combined with other text functions for more complex text manipulation.
- IF LEFT returns an unexpected "0" response, it is most likely due to a circular cell reference or a non-printing character in the cell.
Tips
- Use LEFT in combination with LEN to extract all but the last n characters of a string.
- Combine LEFT with FIND or SEARCH to extract substrings up to a specific character or delimiter.
- When working with dates stored as text, use LEFT to extract the year or month.
- For extracting characters from the end of a string, use the RIGHT function instead.
- Use LEFT with the SUBSTITUTE function to remove unwanted characters from the beginning of a text string.