Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT(apple,3) returns app.

Extract text from the left of a string

– The text from which to extract characters.

– [optional] The number of characters to extract, starting on the left side of text. Default = 1.

The LEFT function extracts a given number of characters from theleftside of a supplied text string. The second argument, callednum_chars, controls the number of characters to extract. Ifnum_charsis not provided, it defaults to 1. Ifnum_charsis greater than the number of characters available, LEFT returns theentiretext string.

To extract the first three characters of January:

If the optional argumentnum_charsis not provided, it defaults to 1:

Ifnum_charsexceeds the string length, LEFT returns the entire string:

When LEFT is used on a numeric value, the result is text:

The LEFT function is often combined with other functions likeLENandFINDto extract text in more complex formulas. For example, to split text at a specific character, use LEFT with the FIND function like this:

FIND returns the position of the character, and LEFT returns all text to the left of that position.Full explanation here.

The LEFT function is used to extract text from theleftside of a text string. Use theRIGHT functionto extract text starting from therightside of the text, and theMID functionto extract from themiddleof text. TheLEN functionreturns the length of text as a count of characters.

LEFT will extract digits from numbers as well as text.

Number formattingis not counted or extracted.

B4 contains the URL: At the core, this formula is extracting characters from the URL, starting from the left, and using the FIND function to figure out how many characters to…

The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT…

FILTER with complex multiple criteria

In this example, we need to construct logic that filters data to include: account begins with x AND region is east, and month is NOT April. The filtering logic of this formula (the include argument) is created by…

At the core, this formula is extracting characters starting from the left, and using the FIND function to figure out how many characters to extract. First, FIND looks for the @ character inside the email address …

Split text string at specific character

In this example, the goal is to split a text string at the underscore(_) character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of…

This formula uses the REPLACE function to replace the first character in a cell with an empty string (). The arguments for REPLACE are configured as follows: old_text is the original value from column B start_num…

The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim. The challenge,…

Convert text date dd/mm/yy to mm/dd/yy

The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as…

Remove file extension from filename

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (.). =LEFT(filename,characters) The FIND…

At a high level, this formula uses LEFT to extract characters from the left side of the name. To figure out the number of characters that need to be extracted to get the last name, the formula uses the FIND function to…

XLOOKUP with complex multiple criteria

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create…

The key to this formula is the array or TRUE and FALSE values constructed with this expression: LEFT(code,1)N Here, each value in the named range code is evaluated with the logical test first…

For a name like Tim Brown, this formula builds an email address like . First, the LEFT function is used to get the first letter from the first name in column C. Usually, the LEFT function gets a …

The first expression uses LEFT and UPPER to capitalize the first letter: =UPPER(LEFT(B5)) No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters…

Inside SUMPRODUCT, we use the LEFT function on the range of numbers like this: LEFT(B5:B11,2) This creates an array of results like this: 25;25;35;45;25…

How to create a complex formula step by step

Some Excel formulas are complex, but all complex formulas are built from simple steps. In this video we build a more complex formula step-by-step.

In this video well look at how dynamic array behavior is native and deeply integrated in Excel. Even older functions can use this new feature.

How to extract text with LEFT and RIGHT

In this video we look at how to use the RIGHT and LEFT functions to extract one or more characters from the beginning or end of a string.

How to extract a first name with a helper column

In this video we look at how to combine the FIND function with the LEFT function to extract the first name from a full name using a formula.

The Excel RIGHT function extracts a given number of characters from therightside of a supplied text string. For example, RIGHT(apple,3) returns ple.

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID(apple,2,3) returns ppl.

The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.

Get over100 Excel Functionsyou should know in one handy PDF.

Hi – Im Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.Read more.

Ive been following Exceljet for a while and would wholeheartedly compliment you on your lucid examples and explanations of the most complicated Excel formulas. This is indeed extremely helpful. -Yossi