LEFT

What does LEFT do?

Returns the first N characters of text from the left.

LEFT syntax

=LEFT(Text, Number_Characters)

The second argument, the number of characters to extract, is optional and by default (if not specified) equals 1, meaning the formula result will be the first character.

If the second argument equals or exceeds the string length, the entire original cell text is returned.

If it equals zero – an empty string is returned.

If a negative number is specified, a #VALUE! error is returned.

LEFT – formatting

It’s important to note that if the first argument is a cell with a date or date-time, or a formula returning them, the LEFT function will convert them to a natural number and only then to a string value.

Therefore, for correct text extraction of part of characters from dates in numeric format, the TEXT function may be needed (see example in the image above).

Formula examples with the LEFT function

Let’s consider examples of using the LEFT function in practice.

Example 1 – extract first word

In this simplest example, we extract the first word in a cell using a combination of LEFT and FIND functions.

=LEFT(A1, FIND(" ", A1) - 1)

The table above was used to extract the first name from a string containing first and last name. The FIND function is used to determine the position of the space between first and last name. Therefore, the length of the first name is the space position minus one character.
The LEFT function extracts the first name based on its length.

How to extract the last name (second word)? See the answer in the description of the RIGHT function.

Note that if a cell contains a single word, FIND finds no space and the formula returns #VALUE!. Add a space to the cell value, and the formula works for any cell. More options, including the first 2, 3 or N words, are in the article on how to get the first words of cells:

=LEFT(A1,FIND(" ",A1&" ")-1)

In Excel 365, the TEXTBEFORE function does the same without calculating positions:

=TEXTBEFORE(A1&" "," ")

Example 2 – extract everything except last character

In combination with the LEN function, LEFT extracts everything except the last character from strings of any length:

=LEFT(A1,LEN(A1)-1)

LEN returns the length of the string, we subtract 1, and LEFT takes that many characters. Replace 1 with N to cut off N characters. More examples, including removing a character only if it’s there, are in the article on how to remove the last characters in Excel.

Example 3 – extract text before first digit (array formula)

In the lighting catalog, the collection name is always represented by a text designation. The article number of a specific product always starts with digits. The task is to extract from the product name its collection without the article number.

So, the task is to extract characters before any first digit. Let’s do this using a combination of LEFT with MIN and SEARCH functions.

The formula borrows mechanics from the first example but is an array formula and looks as follows:

This is what the formula for cell A1 will look like.

=LEFT(A1, MIN(IFERROR(SEARCH({0,9,8,7,6,5,4,3,2,1}, A1), 1000)) - 2)

The formula is entered with the Ctrl + Shift + Enter key combination, like any array formula. But if you have Office 365 or Excel 2021 (and later) – then you can just use the Enter key.

How the formula works:

1. The SEARCH function simultaneously searches for 10 digits listed in the array and returns an array of positions
2. Since some digits return errors when searched, the IFERROR function is used to return a deliberately large number (in this case 1000) for such values
3. The MIN function returns the smallest of the numbers – this will be the position of the first digit in the string
4. Since there’s always a space before the digits, 2 characters are subtracted, not 1. You can be safe in case of missing spaces – leave 1 and remove spaces with the TRIM function
5. The LEFT function returns text up to the calculated position of the last character that comes before the first digit and the space before it

In Excel 365, REGEXEXTRACT makes it much shorter: =TRIM(REGEXEXTRACT(A1,”^\D+”)) returns everything before the first digit, and TRIM removes the trailing space. This is also a good example of when to split text and numbers into separate columns.

Example 4 – get a number from the start of the text

LEFT always returns text, even when it extracts digits. “2024 annual report” → LEFT(A1,4) gives “2024” as text, which is left-aligned, and SUM ignores it. Two minus signs turn the result into a real number:

=--LEFT(A1,4)

All examples with the LEFT function on this site

• Make the first letter in a cell uppercase
• Extract the first N characters of a cell
• Remove the last N words in Excel cells
• Remove the first N characters in Excel cells

In most of these tasks, the formula is only half the job: it has to be typed, filled down, pasted as values and the helper column deleted. With !SEMTools, the same operations take a couple of clicks right in place, without remembering any syntax.

See also:

• Search from the end of a string in Excel
• How to count the days in a month in Excel
• How to create the alphabet in Excel

Summary: when to use LEFT

I hope you found what you were looking for on this page and now know how LEFT works. It’s one of the most used text functions, especially in combination with FIND, SEARCH and LEN. Remember that it always returns text, and for dates you need the TEXT function first.