In Excel, use the LEFT function to return characters from the beginning of a text value: with text in A2 and a request for the first four characters, enter =LEFT(A2,4). LEFT is useful for pulling out codes, initials, prefixes, and other text that starts at a consistent position in a cell.
What the LEFT Function Does
LEFT reads a value from its first character onward and returns the number of characters you specify. It counts from the left edge of the text, not from the first letter or digit it can recognize. Spaces, punctuation, and numbers stored as text all count as characters. The returned result is text, even if the characters happen to look like a number.
For example, suppose A2 contains the text Northwind-042. With that value in A2, =LEFT(A2,9) returns Northwind. With the same value in A2, =LEFT(A2,10) returns Northwind-, because the hyphen is the tenth character. That detail matters when a code contains separators or when the result will be used in another calculation.
If you leave out the character-count argument, LEFT returns just the first character. With Excel in A2, =LEFT(A2) returns E. This default can be handy for extracting an initial, but for most other tasks it is clearer to specify the number of characters.
Arguments and Basic Syntax
LEFT has two arguments: the text to read and, optionally, the number of characters to return. The first argument can be a cell reference, a text value, or a formula that produces text. The second argument must be zero or a positive whole number. In ordinary use, the text is a cell reference and the count is a fixed number or a value calculated elsewhere in the worksheet.
- Text: The source value. It can be a cell such as A2 or text supplied directly in a formula.
- Num_chars: The count of characters to return, beginning at the left. If omitted, Excel uses 1.
With the text Q7-Blue in A2, =LEFT(A2,2) returns Q7. With the same value in A2, =LEFT(A2,0) returns an empty string, so the result appears blank in the cell. A negative count is not allowed and produces a #VALUE! error.
If the requested count is longer than the text, LEFT returns the whole text rather than an error. For instance, if A2 contains AB7, =LEFT(A2,10) returns AB7. This behavior can make a formula safe to fill down a column where some entries are shorter than others.
Extracting Fixed-Length Prefixes
A common use is taking the same number of leading characters from every row. This works well when a data source uses a consistent code format, such as a two-letter region prefix followed by a customer number. Enter a formula in a new column and copy it down to apply the same extraction to the other records.
Suppose A2 contains NW-58320, and the first two characters identify the region. With each source code in column A, enter =LEFT(A2,2) in B2; the result for this example is NW. Fill the formula down column B to extract the prefix for the remaining rows. If the code is intended to have a separator in a fixed position, check that format too: LEFT will return characters as they appear, even when a row is missing a character or contains an unexpected space.
For a column of product identifiers, the extracted prefix can be used for grouping, filtering, or lookup preparation. Keep the original column intact when cleaning imported data. A separate results column makes it easier to compare the extracted value with its source and correct unexpected entries.
Extracting Text Before a Delimiter
Sometimes the part you want has a variable length, so counting characters is not enough. If a hyphen, space, or other separator marks the end of the desired text, combine LEFT with FIND. FIND returns the position of a specified character or text string; subtracting one excludes the separator itself.
Suppose A2 contains INV-2048, with the invoice prefix before the hyphen. With the invoice code in A2, enter =LEFT(A2,FIND("-",A2)-1); it returns INV. For REGION-WEST-12, the same formula returns REGION, because FIND locates the first hyphen.
FIND is case-sensitive, which usually makes no difference for punctuation such as a hyphen. For a text delimiter where letter case should not matter, SEARCH is the case-insensitive alternative. Both functions return a position, so the subtraction by one is what keeps the delimiter out of the LEFT result.
If some rows may not contain a delimiter, the combined formula normally returns an error for those rows because FIND cannot locate the character. Decide what the worksheet should show in that situation instead of hiding the issue without thought. For example, with a code in A2, =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) returns the portion before the first hyphen when present, and returns the original cell value when no hyphen is found.
Numbers, Dates, and Spaces
LEFT can operate on numbers, but it returns text. This distinction matters if you later want to add, sort, or compare the result as a numeric value. Leading zeros can be retained in text, while converting that result back to a number may remove them. For identifiers such as postal codes, account codes, or product numbers, text is often the safer data type because the characters identify something rather than represent a quantity.
Cell formatting can also be misleading. A date may display as 2024-03-15, but Excel stores dates as serial numbers. LEFT works on the underlying value converted to text, not necessarily the date appearance set by the cell’s number format. To take the year from a date in A2 while specifying the display format, use =LEFT(TEXT(A2,"yyyy-mm-dd"),4). If A2 is the date March 15, 2024, the formula returns 2024. The TEXT format string makes the intended date representation explicit.
Leading and trailing spaces count too. If A2 contains Maple with a space before the M, =LEFT(A2,1) returns a space, not M. If spaces are accidental, clean the source or use an appropriate cleanup step before extracting characters. Do not remove spaces automatically if they are meaningful—for example, when the position of each character is part of a fixed-width record.
Using LEFT with Other Text Functions
LEFT is often one part of a larger text-cleaning formula. FIND or SEARCH can locate a delimiter, LEN can measure the text length, and RIGHT or MID can return characters from other positions. Choose the function based on the structure of the data, rather than assuming that a fixed character count will work for every row.
In Microsoft 365 and Excel 2024, TEXTBEFORE can be a more direct option when the goal is to return text before a delimiter. With INV-2048 in A2, =TEXTBEFORE(A2,"-") returns INV. TEXTBEFORE is not available in every older Excel version, so the LEFT-and-FIND approach is useful when a workbook must work in older releases. If a delimiter may appear more than once, check how the chosen method handles multiple occurrences and whether the first or a later separator marks the boundary you need.
LEFT itself works in Microsoft Excel for Microsoft 365 on Windows, Mac, and the web. The function syntax is the same across these versions; what can differ is the formula-entry interface and available newer functions. When copying a formula, confirm that its cell references adjust as intended. A reference such as A2 normally changes to A3 in the next row, while a fixed reference can be written with dollar signs if a formula needs to keep pointing to the same cell.
Common Problems and Checks
When a LEFT formula gives an unexpected answer, inspect the source characters and the count before changing the formula. The function follows positions exactly and does not infer which part of a value is a name, category, or code.
- The result is too short or too long: Count spaces and punctuation as well as letters. Confirm the character position where the desired part ends.
- The result is blank: Check whether the count is zero, the source cell is empty, or the source begins with a space.
- You see #VALUE!: Check for a negative character count. If the formula includes FIND, verify that the delimiter exists in that row.
- A date or number looks different: Remember that LEFT returns text based on the value, not necessarily the cell’s displayed number format. Use TEXT first when a particular display is required.
- Some rows are malformed: Consider validating the source layout or handling missing delimiters explicitly rather than assuming every entry follows the same pattern.
For quality control, compare a few results with their original cells and include edge cases: the shortest expected value, a value with punctuation, and one with a missing or repeated delimiter. A formula that works on the first visible row may still produce poor results elsewhere if the source data is inconsistent.
Frequently Asked Questions
How do I use LEFT in Excel?
Reference the source text and specify how many characters to return. For example, with a text value in A2, =LEFT(A2,3) returns its first three characters. If you omit the count, the function returns the first character.
Can LEFT extract text before a space or hyphen?
Yes. Combine LEFT with FIND, then subtract one from the position FIND returns so the separator is excluded. Check that the separator appears in every relevant row, or handle rows without it separately.
Does LEFT work with numbers?
Yes, but the result is text. That is usually appropriate for identifiers and codes. If you need to calculate with the result as a number, convert it deliberately and consider whether doing so would discard significant leading zeros.
Why does LEFT return a space instead of a letter?
Spaces are characters and are counted from the left edge. A leading space therefore occupies the first position. Inspect the source cell and remove whitespace only if it is genuinely unwanted.
What is the difference between LEFT and TEXTBEFORE?
LEFT returns a specified number of characters from the start. TEXTBEFORE returns the text before a chosen delimiter, which is convenient when the length varies. TEXTBEFORE is available in Microsoft 365 and Excel 2024, while LEFT works in a broader range of Excel versions.
Related
- Searchable Data Validation List in Excel: How to Set Up
- Sample Customer List Excel: Fields, Template, and Setup
- Sales Order Tracking Excel Template: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Numbers in Word Mail Merge: A Step-by-Step GuideTo round numbers in a Word mail merge, add a numeric picture switch to the merge field, such…
- Create an Invoice in Excel With FormulasYes: create columns for quantities and unit prices, calculate each line total, add the lines for a subtotal,…
- How to Filter Excel by Date: Ranges, Months, and YearsTo filter Excel by date, select a cell in your data, choose Data > Filter, open the arrow…
- Excel EOMONTH Formula: Find the Last Day of a MonthUse Excel’s EOMONTH function to return the last day of a month: enter =EOMONTH(start_date, months), replacing the arguments…
- Attendance Present Formula in Excel: Count It CorrectlyTo count attendance entries marked P in Excel, use COUNTIF: with the attendance marks in B2:B31, enter =COUNTIF(B2:B31,"P").…
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.