To return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with the source cell and 3 with the number of characters you need. LEFT is useful for pulling out a prefix such as a product code, area code, or first few letters of a name. You need a workbook with the source text and a cell for the result; no add-in is required.
What You’ll Need
- Excel for Microsoft 365 on Windows, or another recent Excel version. LEFT is a long-standing worksheet function and is also available in Excel for Mac and Excel for the web.
- A cell or column containing the text you want to shorten, plus a blank cell where you can enter the formula.
- For repeatable results, a consistent pattern in the source values. For example, codes should begin with the same number of prefix characters if you plan to extract a fixed-length prefix.
Step-by-Step
- Identify the source text. Find the column or cells that contain the values to extract from. For example, imagine A2 contains the text
INV-48217. LEFT reads from the first character on the left and does not change the source cell. It returns a result in the cell containing the formula, so choose a blank column beside your data if you want to keep both the original and extracted values. - Choose how many characters to return. Count the characters you need, including spaces and punctuation if they occur within the part you want. To get the three letters in a code such as
NYC-104, use 3. To include the hyphen as well, use 4. LEFT counts characters from the start; it does not search for a particular letter or automatically recognize a word boundary. - Select the result cell and enter the formula. If the source value is in A2 and you want its first three characters, click B2 and type the formula below. The first argument is the source text or cell reference. The second argument is the number of characters to return.
With the source text in A2 and the desired result in B2, enter:
=LEFT(A2,3)
If A2 contains INV-48217, the result is INV. Press Enter to calculate it. In Excel for Mac, enter the formula in the same way and press Return. Excel for the web also uses the same formula syntax.
- Check the displayed result. Confirm that the first characters are the ones you intended. If you see fewer characters than expected, check the number in the formula and the source value. If the source is shorter than the requested length, LEFT simply returns all of the available characters; it does not add spaces or zeros to fill the remainder.
- Use the optional argument when you only need one character. If you leave out the number of characters, LEFT returns the first character by default. With text in A2, enter:
=LEFT(A2)
If A2 contains West, this returns W. You can also specify 1 explicitly as =LEFT(A2,1); that version can be easier for someone else to understand when reviewing the worksheet.
- Fill the formula down for more rows. If each row has a source value in column A and the first result is in B2, select B2 and drag the small square at its lower-right corner down the column. You can also copy B2 and paste it into the other result cells. Excel adjusts the relative reference as it fills, so the next row will refer to A3, then A4, and so on. Review a few results, especially if the source data has mixed formats.
- Keep results as formulas or make them fixed values. A formula result updates when its source changes. If you need a permanent snapshot, copy the result cells and use Home > Paste > Paste Values. This replaces the formulas with their current results, so do this only when you no longer need the results to update automatically.
The general syntax is =LEFT(text, [num_chars]). The brackets in this syntax mean that num_chars is optional; do not type the brackets into a worksheet formula. If num_chars is omitted, Excel uses 1. If it is 0, LEFT returns an empty text string. A negative number is not allowed and returns a #VALUE! error.
You can also combine LEFT with another function when the number of characters varies. For instance, if A2 contains INV-48217 and you want everything before the hyphen, find the hyphen and return the characters before it. With the code in A2 and the result in B2, enter:
=LEFT(A2,FIND("-",A2)-1)
This returns INV. FIND returns the hyphen position, and subtracting 1 excludes the hyphen itself. This approach is useful when prefixes are different lengths but share a delimiter. It returns an error if the delimiter is missing, so use it only when the source pattern is predictable or handle missing delimiters separately.
Common Mistakes
- Requesting the wrong number of characters. LEFT counts every character, including spaces, hyphens, and other punctuation. If you need the first four characters of
AB-123, the result isAB-1, not four letters or four digits. Decide whether punctuation belongs in the output before setting the count. - Expecting LEFT to return a word or find a separator by itself. A fixed count works only when the part you need has a fixed length. If the prefix length varies, combine LEFT with a function such as FIND, as in the delimiter example above. Check that the separator is present in every relevant row.
- Using a negative character count. A negative num_chars argument produces
#VALUE!. If you are calculating the count from another function, check that the calculation cannot fall below zero. A count of zero is allowed, but it returns a blank-looking result. - Assuming numbers are always treated like their visible formatting. LEFT works with text. When you point it at a numeric cell, Excel converts the stored number to text for the function, which may not match the way the number is displayed. For example, a number formatted with leading zeros may be stored without those zeros. If the zeros matter, store the code as text or convert it to text using an appropriate format before extracting characters.
- Overwriting the source data. Entering the formula in the same cell as the source would replace that source value. Put the formula in a separate cell or column, and keep a backup if you are transforming important data.
Tips
- Use cell references rather than retyping values. A formula such as
=LEFT(A2,3)is easier to fill down and updates if A2 changes. A formula with text typed directly into it can be useful for a one-off test, but it is less practical for a changing list. - Standardize the source column first. Imported data may contain extra spaces or inconsistent separators. LEFT counts spaces just like letters, so a leading space can make the result look wrong. If you suspect inconsistent spacing, inspect the source cell and clean the data before relying on a fixed character count.
- Use a table for growing lists. If your source data is an Excel table, a formula in a new table column can fill down automatically as rows are added. Check that the formula refers to the intended source column and that the extracted values are consistent.
- Know when LEFT is not the right tool. LEFT is for characters at the beginning of a value. Use RIGHT when you need characters from the end, or MID when you need characters starting at a position in the middle. If the number of characters changes by row, use a delimiter-based formula rather than guessing a fixed count.
- Be aware of regional formula separators. The formulas here use commas, as in standard English-language examples. Depending on your Windows regional settings, Excel may require semicolons between arguments. If Excel rejects a formula with commas, check the separator settings used by your installation rather than changing the function name.
Troubleshooting
The formula displays as text instead of calculating. The cell may be formatted as Text, or the formula may have an apostrophe before the equals sign. Change the cell format to General, edit the cell, and press Enter again. Also confirm that the entry begins with an equals sign.
You get #VALUE!. Check that the character count is not negative. If you used FIND to locate a separator, verify that the separator actually appears in the source cell. FIND returns an error when it cannot find the requested character, and that error carries through to LEFT.
The answer is blank. A count of zero returns an empty string, and a source cell that appears blank may contain an empty string from another formula. Check the second argument and inspect the source cell. If the formula uses a calculated count, make sure that calculation returns a positive number.
The result has unexpected spaces or punctuation. LEFT includes those characters in its count. Look closely at the start of the source value, including invisible-looking spaces, and adjust the count or clean the data. If the issue appears only on some rows, compare their formats and source patterns rather than assuming every row follows the same structure.
Leading zeros disappear or the extracted result looks different from the cell display. Check whether the source is stored as a number or text. Number formatting changes how a value looks but does not necessarily change the underlying value that LEFT reads. If zeros are part of an identifier, keep the identifier as text or create text with the required format before extracting from it.
Excel rejects the formula when you type it. Confirm that parentheses are balanced and that the argument separator matches your regional settings. In installations that use semicolons, replace the commas between arguments with semicolons. Function names are not case-sensitive, so typing left in lowercase is not a problem.
Frequently Asked Questions
What does the LEFT function do in Excel?
It returns a specified number of characters from the beginning, or left side, of a text value. It leaves the original cell unchanged and places the result where you enter the formula.
What happens if I leave num_chars out?
LEFT returns one character. You can omit the second argument or enter 1 explicitly when you want the first character only.
Can LEFT extract characters before a hyphen or space?
Yes. Combine LEFT with FIND, then subtract 1 from the separator position so the separator is not included. Make sure the separator appears in the source value; otherwise FIND returns an error.
Does LEFT work with numbers?
It can return characters from a numeric value after Excel converts it to text, but the result may not follow the cell’s display formatting. For identifiers where leading zeros matter, use text values or convert the number to a correctly formatted text value first.
Can I use LEFT in Excel for Mac or Excel for the web?
Yes. The function and formula syntax are the same in current Excel for Mac and Excel for the web. The steps for entering and filling a formula are also similar, although ribbon layout and keyboard shortcuts can differ slightly by platform.
How do I return everything before a delimiter that moves?
Use a delimiter-based formula such as the FIND example above. LEFT alone needs a fixed character count, while FIND identifies the position of a delimiter that may appear at different places in different rows.
Related
- Excel Data Analysis Sampling: Methods, Steps, and Uses
- Excel Data Analysis Skills: What They Include and Why
- Excel Solver for Data Analysis: Setup, Models, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find the Minimum in Excel Using the MIN FunctionTo find the smallest number in a range, use Excel’s MIN function. If your values are in cells…
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- VLOOKUP Examples Using Two SpreadsheetsTo look up a value in a separate Excel workbook, put the lookup key in your current sheet…
- Excel Table of Contents: Create a Clickable Sheet IndexExcel does not have a dedicated command that automatically creates a table of contents for every worksheet. The…
- How to Multiply Hours in Excel: Formulas and FormattingTo multiply a duration by a factor in Excel, enter the duration in A2 and the factor in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.