*The tutorial shows how to use the LEFT function in Excel to get a substring from the beginning of the text string, extract text before a certain character, force a Left formula to return a number, and more. *

Among many different functions that Microsoft Excel provides for manipulating text data, LEFT is one of the most widely used. As its name suggests, the function allows you to extract a certain number of characters starting at the left side of a text string. However, Excel LEFT is capable of much more than its pure essence. In this tutorial, you will find a couple of basic Left formulas to understand the syntax, and then I will show you a few ways in which you can take the Excel LEFT function well beyond its basic usage.

## Excel LEFT function - syntax

The LEFT function in Excel returns the specified number of characters (substring) from the start of a string.

The syntax of the LEFT function is as follows:

Where:

**Text**(required) is the text string from which you want to extract a substring. Usually it is supplied as a reference to the cell containing the text.**Num_chars**(optional) - the number of characters to extract, starting on the left side of the string.- If
*num_chars*is omitted, it defaults to 1, meaning that a Left formula will return 1 character. - If
*num_chars*is greater than the total length of*text*, a Left formula will return all of*text*.

- If

For example, to extract the first 3 characters from the text in cell A2, use this formula:

`=LEFT(A2, 3)`

The following screenshot shows the result:

**Important note!**LEFT belongs to the category of Text functions, therefore the result of a Left formula is always a

**text string**, even if the original value from which you extract characters is a number. If you are working with a numeric dataset and want the LEFT function to return a number, use it in conjunction with the VALUE function as demonstrated in this example.

## How to use LEFT function in Excel - formula examples

Apart from extracting text from the left of a string, what else can the LEFT function do? The following examples show how you can use LEFT in combination with other Excel functions to solve more complex tasks.

### How to extract a substring before a certain character

In some cases, you may need to extract the part of the text string that precedes a specific character. For example, you may want to pull the first names from a column of full names or get the country codes from a column of phone numbers. The problem is that each name and each code contains a different number of characters, and therefore you cannot simply supply a predefined number to the *num_chars* argument of your Left formula like we did in the above example.

If the first and last names are separated by a space, the problem boils down to working out the position of the space character in a string, which can be easily done by using either SEARCH or FIND function.

Supposing the full name is in cell A2, the position of the space is returned by this simple formula: SEARCH(" ",A2)). And now, you embed this formula in the *num_chars* argument of the LEFT function:

`=LEFT(A2, SEARCH(" ", A2))`

To improve the formula a bit further, get rid of the trailing space by subtracting 1 from the Search formula result (not visible in cells, trailing spaces may cause many problems especially if you plan to use the extracted names in other formulas):

`=LEFT(A2, SEARCH(" ", A2)-1)`

In the same fashion, you can extract the country codes from a column of telephone numbers. The only difference is that you use the Search function to find out the position of the first hyphen ("-") rather than a space:

`=LEFT(A2, SEARCH("-", A2)-1)`

Wrapping up, you can use this generic formula to get a substring that precedes any other character:

*string*, SEARCH(

*character*,

*string*) - 1)

### How to remove the last N characters from a string

You already know how to use the Excel LEFT function to get a substring from the start of a text string. But sometimes you may want to do something different - remove a certain number of characters from the end of the string and pull the rest of the string into another cell. For this, use the LEFT function in combination with LEN, like this:

*string,*LEN(

*string*) -

*number_of_chars_to_remove*)

The formula works with this logic: the LEN function gets the total number of characters in a string, then you subtract the number of unwanted characters from the total length, and have the LEFT function return the remaining characters.

For example, to remove the last 7 characters from text in A2, use this formula:

`=LEFT(A2, LEN(A2)-7)`

As shown in the screenshot below, the formula successfully cuts off the " *- ToDo"* postfix (4 letters, a hyphen and 2 spaces) from the text strings in column A.

### How to force the LEFT function to return a number

As you already know, the Excel LEFT function always returns text, even when you are pulling a few first digits from a number. What it means to you is that you won't be able to use the results of your Left formulas in calculations or in other Excel functions that operate on numbers.

So, how do you make Excel LEFT to output a number rather than a text string? Simply by wrapping it in the VALUE function, which is designed to convert a string representing a number to a number, like this: `VALUE(LEFT())`

For example, to extract the first 2 characters from the string in A2 and convert the output into numbers, use this formula:

`=VALUE(LEFT(A2,2))`

The result will look something similar to this:

As you can see in the screenshot above, the numbers in column B obtained with a Value Left formula are right-alighted in cells, as opposed to left-aligned text in column A. Since Excel recognizes the output as numbers, you are free to sum and average those values, find the min and max value, and perform any other calculations.

These are just a few of many possible uses of LEFT in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download the Excel LEFT function sample worksheet.

For more Left formula examples, please check out the following resources:

## Excel LEFT function not working - reasons and solutions

If the Excel LEFT function does not work properly in your worksheets, it's most likely because of one of the following reasons.

### 1. Num_chars argument is less than zero

If your Excel Left formula returns the #VALUE! error, the first thing for you to check is the value in the *num_chars* argument. If it's a negative number, just remove the minus sign and the error will be gone (of course, it's very unlikely that someone will put a negative number there of purpose, but to err is human :)

Most often, the VALUE error occurs when the *num_chars* argument is represented by another function. In this case, copy that function to another cell or select it in the formula bar and press F9 to see what it equates to. If the value is less than 0, then check the function for errors.

To better illustrate the point, let's take the Left formula we've used in the first example to extract the country phone codes: LEFT(A2, SEARCH("-", A2)-1). As you may remember, the Search function in the *num_chars* argument calculates the position of the first hyphen in the original string, from which we subtract 1 to remove the hyphen from the final result. If I accidentally replace -1, say, with -11, the formula would through the #VALUE error because the *num_chars* argument equates to a negative numbers:

### 2. Leading spaces in the original text

In case your Excel Left formula fails for no obvious reason, check the original values for leading spaces. If you have copied your data from the web or exported from another external source, many such spaces may lurk unnoticed before the text entries, and you will never know they are there until something goes wrong. The following image illustrates the problem:

To get rid of the leading spaces in your worksheets, use the Excel TRIM function or the Text Toolkit add-in.

### 3. Excel LEFT does not work with dates

If you attempt use the Excel LEFT function to get an individual part of a date (such as day, month or year), in most cases you will only retrieve the first few digits of the number that represents that date. The point is that in Microsoft Excel, all dates are stored as integers representing the number of days since January 1, 1900, which is stored as number 1 (for more information, please see Excel date format). What you see in a cell is just a visual representation of the date and its display can easily be changed by applying a different date format.

For example, if you have the date 11-Jan-2017 in cell A1 and you try to extract the day by using the formula LEFT(A1,2), the result would be 42, which is the first 2 digits of number 42746 that represents January 11, 2017 in the internal Excel system.

To extract a specific part of a date, use one of the following functions: DAY, MONTH or YEAR.

In case your dates are entered as text strings, the LEFT function will work without a hitch, as shown in the right part of the screenshot:

This is how you use the LEFT function in Excel. I thank you for reading and hope to see you again next week.

This is very helpful for me.thank you

I wants to know whether is there any function in excel to limit a cell to one text character only and string completed and cursor automatically moves to other right cell in excel to write in that cell, just as in form filling with one character in one cell and the other in the other cell,

Hello,

Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).

You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

Sorry we can’t assist you better.

OMG, I love you. I could tell the =Left was text but didn't know what to do about it!

I have a column with names

John Robbins

Alok singh

Rohit

Raghu Prabhu

if I use =LEFT(A2, SEARCH(" ", A2)-1)

it is working fine with records 1,2 and 4 but with 3 it is showing an error. how do i incorporate this?

regards

Raghu Prabhu

Raghu:

I think this is what you're looking for:

Sample ## will show #ERROR because there is no space in that record. So, you might want to add the IFERROR function in front of the formula like this:

=IFERROR(LEFT(A5,SEARCH(" ",A5)-1),"No Space")

The IFERROR function will return a message such as "No Space" if the formula would otherwise return #ERROR.

I suppose you could use this formula:

=IFERROR(LEFT(A5,SEARCH(" ",A5)-1),"A5")

which would return the value of A5. If you copy this down the column you will see the A5 will change to the relative cell address.

Other than this, I guess you'll need to add a space to the record.

If I have a list of numbers, all with various amounts of digits (some 2, 3, 4, 5, or 6 digits long) and I want them all to be uniform and add a prefix (like a P in front) so they are all P00000000, what formula would I use?

Clarification:

If I have a list of numbers, all with various amounts of digits (some 2, 3, 4, 5, or 6 digits long) and I want them all uniform in format, with a prefix (example below), what formula would I use?

48492 = P00048492

23 = P00000023

600 = P00000600

110291 = P00110291

Hi Lori

Please use below formula. I'm Assuming the length should be 9, you change it for your requirement.

=IF(LEN(B2)<9,CONCATENATE("P",REPT(0,8-LEN(B2)),B2))

Hello,

I want to sum in a raw (let's say F10:AJ10) numbers and something like this:

7, 9, 11, I7, 5, I9, 3, 10, I2.

So I want to sum all numbers and the digits after the "I" (7,9,2). These may appear random, no exactly place.

Dan:

I found this formula on the Microsoft site. I think it is what you're looking for.

Where the data is in I29:R29 this is entered in an empty cell as an array. This means, in the formula bar enter this formula then with the cursor somewhere in the formula click CTRL Shift Enter together. This action will tell Excel this is an array and you'll see the curly brackets surrounding the formula.

=SUM(IF(I29:R29"",IF(NOT(ISNUMBER(--LEFT(I29:R29,1))),--MID(I29:R29,2,256))))+SUM(I29:R29)