{"id":6908,"date":"2022-08-15T20:36:51","date_gmt":"2022-08-15T15:06:51","guid":{"rendered":"https:\/\/exceltrick.com\/?p=6908"},"modified":"2023-03-18T23:44:48","modified_gmt":"2023-03-18T18:14:48","slug":"excel-left-function","status":"publish","type":"post","link":"https:\/\/exceltrick.com\/functions\/excel-left-function\/","title":{"rendered":"Excel LEFT Function \u2013 How To Use"},"content":{"rendered":"<p>The LEFT function is one of the many common and widely used text\/string functions offered by Microsoft Excel.<\/p>\n<p>The LEFT function enables you to extract a predetermined number of characters from a text string beginning on the left side. You just have to provide the input text or the reference cell to the function and the number of characters you wish to retrieve from the beginning of the text.<\/p>\n<p>In this post we will take a closer look at the LEFT function and explore the value it can provide when it comes to text manipulation in Excel.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6914\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/Excel-LEFT-Function.png\" alt=\"Excel LEFT Function\" width=\"753\" height=\"396\" title=\"\">\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_53 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\" role=\"button\"><label for=\"item-69f428c3828c4\" ><span class=\"\"><span style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input aria-label=\"Toggle\" aria-label=\"item-69f428c3828c4\"  type=\"checkbox\" id=\"item-69f428c3828c4\"><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Syntax\" title=\"Syntax\">Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Important_Characteristics_of_the_LEFT_Function\" title=\"Important Characteristics of the LEFT Function\">Important Characteristics of the LEFT Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Examples_of_LEFT_Function\" title=\"Examples of LEFT Function\">Examples of LEFT Function<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Example_1_%E2%80%93_Basic_Functionality\" title=\"Example 1 &#8211; Basic Functionality\">Example 1 &#8211; Basic Functionality<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Example_2_%E2%80%93_Extracting_Substring_Before_a_Particular_Character\" title=\"Example 2 &#8211; Extracting Substring Before a Particular Character\">Example 2 &#8211; Extracting Substring Before a Particular Character<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Example_3_%E2%80%93_LEFT_Function_with_SEARCH_Function\" title=\"Example 3 &#8211; LEFT Function with SEARCH Function\">Example 3 &#8211; LEFT Function with SEARCH Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Example_4_%E2%80%93_How_to_remove_last_few_characters_from_string\" title=\"Example 4 &#8211; How to remove last few characters from string\">Example 4 &#8211; How to remove last few characters from string<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#LEFT_Function_with_Dates\" title=\"LEFT Function with Dates\">LEFT Function with Dates<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#Forcing_LEFT_Function_to_Return_Numbers\" title=\"Forcing LEFT Function to Return Numbers\">Forcing LEFT Function to Return Numbers<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/#LEFT_Function_vs_RIGHT_Function_vs_MID_Function\" title=\"LEFT Function vs RIGHT Function vs MID Function\">LEFT Function vs RIGHT Function vs MID Function<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Syntax\"><\/span>Syntax<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax of the LEFT function is mentioned below:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(text, [num_chars])<\/code><\/div>\n<p style=\"margin: 0px;\"><strong>Arguments:<\/strong><\/p>\n<p style=\"padding-left: 30px;\">&#39;<em>text<\/em>&#39; &#8211; This is a required argument that may be supplied with either the text string in double quotes or a cell reference to the text.<\/p>\n<p style=\"padding-left: 30px;\">&#39;<em>num_chars<\/em>&#39; &#8211; This is an optional argument with default as 1. It sets the number of characters you wish to extract from left to right.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Important_Characteristics_of_the_LEFT_Function\"><\/span>Important Characteristics of the LEFT Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ul>\n<li>The LEFT function is a text function, meaning the result will always be a text string. Even if the input value is numerical, the return value will be in text format.<\/li>\n<li>If <em>num_chars <\/em>exceeds the total text length, it will return the complete text.<\/li>\n<li>If the <em>text <\/em>argument is provided as a text string without double quotes, it returns a <a href=\"https:\/\/exceltrick.com\/functions\/name-error-excel\/\">#NAME error<\/a>.<\/li>\n<li>If the value in the <em>num_chars<\/em> argument is non-numeric, the function yields a #VALUE error.<\/li>\n<li>If <em>num_chars<\/em> is not an integer, the function will round off the number to the lower integer and return the applicable characters.<\/li>\n<li>If <em>num_chars<\/em> is assigned a negative number, the LEFT function will return a <a href=\"https:\/\/exceltrick.com\/functions\/value-error-excel\/\">#VALUE! error<\/a>.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Examples_of_LEFT_Function\"><\/span>Examples of LEFT Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Let&#39;s try to understand the LEFT function using examples. You can always try all the examples on your own in a spreadsheet to get a better understanding.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example_1_%E2%80%93_Basic_Functionality\"><\/span>Example 1 &#8211; Basic Functionality<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Here in this dataset, we have taken 5 different scenarios of using the basic functionality of the LEFT function. First, let&#39;s see how the function behaves with text, special characters, space, and numbers.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6915\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_01.png\" alt=\"Examples of LEFT Function\" width=\"733\" height=\"235\" title=\"\">\n<p>In case 1, cell C3 contains the text &#39;Simple Sentence&#39;. We want to extract the first three letters of the text. So, the simple formula goes like:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(C3,3)<\/code><\/div>\n<p>We use the formula in E3 by giving the cell reference C3 as the <em>text<\/em> parameter and 3 as <em>num_char<\/em> as we want the first three characters. First three characters from left to right would be &#39;Sim,&#39; hence the return value in cell E3.<\/p>\n<p>Let&#39;s find out how the LEFT function behaves with the special characters in case 2. The input value contains a double quote, dollar and exclamation marks in the text string in cell C4. We are trying to extract the first 6 characters of the text; therefore, the formula will be as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(C4,6)<\/code><\/div>\n<p>The input data is &quot;$pec!al Character&quot; where double quote, dollar and exclamation mark all are counted as characters. The result is &quot;$pec!, which is the first 6 characters from left. This simply tells you that all special characters, even double quotes and spaces, are counted when using the LEFT function.<\/p>\n<p>Try the same thing with numbers. Here with case 3, in cell C5 the input parameter is 123467. To extract 4 characters from the beginning, The formula will be as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(C5,4)<\/code><\/div>\n<p>By now, you know the result. It will be 1234. It should be noted here that the return value in cell E5 will be in text format rather than in numerical format. We will discuss this case in detail in the below section.<\/p>\n<p>What will happen if you omit the second parameter, <em>num_chars, <\/em>in the LEFT function? As mentioned earlier, the default value for this parameter is 1. In case 4, we can use this formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(C6)<\/code><\/div>\n<p>The input data here is in C6, which is &#39;Default Value&#39;. As we have not mentioned the second parameter, the result will be one character \u2013 D, as that is the first character from the left.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example_2_%E2%80%93_Extracting_Substring_Before_a_Particular_Character\"><\/span>Example 2 &#8211; Extracting Substring Before a Particular Character<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Suppose you want to extract the first name from a list of full names, find the country code from a list of phone numbers, or determine the state code from a car number. You can use the LEFT function, but now the issue is that since the number of characters in each case may vary, you would not be able to use a fixed numerical value in the <em>num_char<\/em> argument.<\/p>\n<p>But, if there is a space or special character in the data that is separating the substring you want from the remainder text, we can easily find the position of the special character using either the SEARCH or <a href=\"https:\/\/exceltrick.com\/functions\/excel-find-function\/\">FIND function<\/a> and then use it with the LEFT function to return the values before that particular character.<\/p>\n<p>Counting from the left, the SEARCH function returns the position of a character or text string. In this case, we have the addresses of people, which are a combination of house numbers and street names separated by a space. We want to obtain the house numbers alone in a separate column. This can be achieved using the LEFT function combined with the <a href=\"https:\/\/exceltrick.com\/functions\/search-function-in-excel\/\">SEARCH function<\/a>.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6916\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_02.png\" alt=\"Extracting Substring Before a Particular Character\" width=\"595\" height=\"343\" title=\"\">\n<p>The input here is the full address in B2, and the position of the space is found and returned using the following formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">SEARCH<\/span>(\" \",B2)<\/code><\/div>\n<p>Now, enter the SEARCH function in the LEFT formula in the <em>num_chars<\/em> argument. By doing this, you are telling the LEFT function the position of space in the address. The final formula to be used will be:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(B2,<span class=\"function\">SEARCH<\/span>(\" \",B2))<\/code><\/div>\n<p>This way, Excel will determine the position of the space character and return the characters before it.<\/p>\n<p>Let&#39;s take another example for better understanding.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example_3_%E2%80%93_LEFT_Function_with_SEARCH_Function\"><\/span>Example 3 &#8211; LEFT Function with SEARCH Function<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The data set we use in this example consists of product names in column B, which are shoe names with their brand names. To check which brand is more popular in shoes these days, we wish to segregate brand names from the product names. Lucky for us, the brand name is followed by a hyphen &#39;-&#39; and then the product name.<\/p>\n<p>This time, instead of a space character, we look for the dash (-) when using the LEFT function. In cell B2, the input value is &#39;SKETCHERS-Go Run Consistent&#39;, and we want to extract the data before &#39;-&#39;. Now, the formula becomes:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(B2,<span class=\"function\">SEARCH<\/span>(\"<span class=\"string\">-<\/span>\",B2))<\/code><\/div>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6917\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_03.png\" alt=\"LEFT Function with SEARCH Function\" width=\"623\" height=\"315\" title=\"\">\n<p>SEARCH function finds the position of &#39;-&#39;, and the LEFT function extracts the characters before it. The desired result is in cell C2 which is SKETCHERS-.<\/p>\n<p>Suppose you want to improve the formula and remove the &#39;-&#39; from the earlier result. You can reduce 1 more character from the LEFT formula result. So simply using it like it is mentioned below. (the &quot;-1&quot; argument doesn&#39;t belong to the SEARCH function but the LEFT function as shown in the screenshot below:<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6918\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_05.png\" alt=\"LEFT Function with SEARCH Function\" width=\"640\" height=\"315\" title=\"\">\n<p>By telling the LEFT function to subtract one more character from the result when the &#39;-&#39; is found. This is an easy inclusion in the formula and the complete formula becomes:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(B2,<span class=\"function\">SEARCH<\/span>(\"<span class=\"operator\">-<\/span>\",B2)<span class=\"operator\">-<\/span>1)<\/code><\/div>\n<p>To have the LEFT function return one character less from the right, the <em>num_chars<\/em> argument has also been entered as &quot;-1&quot;. The effect can be seen below as the brand names are returned without hyphens:<\/p>\n<p>Now you have clean data without the special character (-).<\/p>\n<p>You can now easily extract a substring from data before a special character. Now, what if you want to remove certain characters from the end of the string and extract the rest.<\/p>\n<p>We are left with clean data without the special character (-). You can now easily extract a substring from the data before a special character. But what if you only want to remove certain characters from the end of the string?<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example_4_%E2%80%93_How_to_remove_last_few_characters_from_string\"><\/span>Example 4 &#8211; How to remove last few characters from string<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To remove the last N characters from a string, we can try to use the <a href=\"https:\/\/exceltrick.com\/functions\/excel-len-function\/\">LEN function<\/a> with the LEFT function.<\/p>\n<p>This is how it works: the LEN function determines the number of characters in a string and returns a numerical value. We can then subtract the required number of characters from the total length and return the remaining characters using the LEFT function.<\/p>\n<p>In this example data set, we have a list of Employee IDs in column B that are a combination of department names followed by unique 5-digit IDs.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6919\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_06.png\" alt=\"How to remove last few characters from string\" width=\"420\" height=\"266\" title=\"\">\n<p>We wish to know the department of each employee and, therefore, want to extract the department name from the employee ID. This cannot be done using the traditional usage of the LEFT function since the number of characters for each department is different.<\/p>\n<p>Also, we cannot use the SEARCH or FIND function since there is no space or special character between the department name and employee ID. Hence, we use the LEFT function in combination with the LEN function.<\/p>\n<p>The input string \u2013 Employee ID is in cell B2. The number of characters can be calculated in the string using the LEN function, which in this case would return a numerical value of 11. Then upon using this in the <em>num_chars<\/em> argument of the LEFT function and subtracting the last 5 characters (as we know that the employee ID is fixed with 5 characters), we get the desired result.<\/p>\n<p>The final formula goes as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(B2,<span class=\"function\">LEN<\/span>(B2)<span class=\"operator\">-<\/span>5)<\/code><\/div>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6920\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_07.png\" alt=\"How to remove last few characters from string\" width=\"591\" height=\"266\" title=\"\">\n<p>You have your department names as required in column C.<\/p>\n<p>Let&#39;s mix it up even more and use the LEFT function with dates. Will it behave in the same manner?<\/p>\n<p>Let&#39;s find out.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"LEFT_Function_with_Dates\"><\/span>LEFT Function with Dates<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When working with dates in Excel, they are only displayed as dates due to cell formatting. Otherwise, they are actually serial numbers in Excel. If we try to use the LEFT function on a date, it will return the first few characters of the number corresponding to that date. For Excel, 01 January 1900 is 1, and then it keeps adding as we add a day.<\/p>\n<p>For instance, 01 July 2022 is represented by the number 44743. Using the LEFT function to retrieve two characters from cell B3 holding the date 01-07-2022 will produce the value &quot;44,&quot; as seen in C2.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6921\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_08.png\" alt=\"LEFT Function with Dates\" width=\"485\" height=\"140\" title=\"\">\n<p>Now there is one more way to utilize the LEFT function with dates. It is to insert the date directly as a text argument. So, we use the following formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(\"<span class=\"string\">01 July 2022<\/span>\",2)<\/code><\/div>\n<p>In this case since the date is supplied as text so the LEFT function will simply extract the first two characters, i.e. the date 01, from the input string.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6922\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_08b.png\" alt=\"LEFT Function with Dates\" width=\"509\" height=\"141\" title=\"\">\n<p>For extracting Years, Month or Day from Dates in Excel we have better options in the form of <a href=\"https:\/\/exceltrick.com\/functions\/excel-year-function\/\">YEAR<\/a>, <a href=\"https:\/\/exceltrick.com\/functions\/excel-month-function\/\">MONTH<\/a> and <a href=\"https:\/\/exceltrick.com\/functions\/excel-day-function\/\">DAY<\/a> functions.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Forcing_LEFT_Function_to_Return_Numbers\"><\/span>Forcing LEFT Function to Return Numbers<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As we already know by now, the LEFT function is a text function; therefore, any value it returns will be in the text format. In the earlier example of using the LEFT function with &#39;123467&#39; as the input string, the number of desired characters is returned but as text. This means you may not be able to use it for calculations. Let&#39;s try it with an example.<\/p>\n<p>In the example below, we are trying to use the LEFT function with numbers. We wish to multiply\u00a0900 with 15 in cell B2 and then extract the first 3 characters from the result. So, the formula used is as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(900<span class=\"operator\">*<\/span>15,3)<\/code><\/div>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6923\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_09.png\" alt=\"Forcing LEFT Function to Return Numbers\" width=\"532\" height=\"264\" title=\"\">\n<p>As per basic mathematical calculation, 900*15 is 13500. Extracting the first 3 characters from it gives the result 135.<\/p>\n<p>The important observation here is that the result of our formula is returned as a text value by default. Excel aligns numbers to the right and text to the left, also seen in the example below.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6924\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_10.png\" alt=\"Forcing LEFT Function to Return Numbers\" width=\"447\" height=\"209\" title=\"\">\n<p>By this logic, the result in cell B2 should be aligned to the right. But, as you can notice, it is left aligned indicating the text format. Another way to check whether the value in cell B2 is text or number is by using the ISNUMBER function.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6925\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_11.png\" alt=\"Forcing LEFT Function to Return Numbers\" width=\"615\" height=\"260\" title=\"\">\n<p>As you can see, when we use the ISNUMBER function to check whether the value in B2 is a number or not., the return value is FALSE.<\/p>\n<p>So, how to convert this text format to number format? The solution is easy.<\/p>\n<p>Use the VALUE function. The VALUE function converts text representing a number into a number. Using the VALUE function with the LEFT function extracts a numerical value from the left as text which then gets converted to a number. The formula used will be as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">VALUE<\/span>(<span class=\"function\">LEFT<\/span>(900<span class=\"operator\">*<\/span>15,3))<\/code><\/div>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6926\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_12.png\" alt=\"Forcing LEFT Function to Return Numbers\" width=\"549\" height=\"188\" title=\"\">\n<p>As shown in the example below, we have used the formula in cell B2. To confirm the datatype of the returned value we have again used the ISNUMBER function which returns TRUE. Also, note that the result values in column B are now right-aligned.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6927\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_12-a.png\" alt=\"Forcing LEFT Function to Return Numbers\" width=\"759\" height=\"189\" title=\"\">\n<h2><span class=\"ez-toc-section\" id=\"LEFT_Function_vs_RIGHT_Function_vs_MID_Function\"><\/span>LEFT Function vs RIGHT Function vs MID Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You have now comprehensively understood how to use the LEFT function in different scenarios. What if you wish to extract the characters from right to left or from the middle of a string? You can do so by using the RIGHT function and MID function respectively.<\/p>\n<p>Let&#39;s compare the three functions using an example for a better understanding. Here we have taken Input Text as &#39;Smart, Easy &amp; Effective&#39;, in cell A2. We are using the LEFT function in cell B2, MID function in cell C2, and RIGHT function in cell D2.<\/p>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-6928\" src=\"https:\/\/exceltrick.com\/wp-content\/uploads\/2022\/08\/excel-left-function_13.png\" alt=\"LEFT Function vs RIGHT Function vs MID Function\" width=\"838\" height=\"163\" title=\"\">\n<p>For the LEFT function, we are trying to extract the first word &#39;Smart&#39; from the left which is 5 characters long. As we know to do this we are using the following formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">LEFT<\/span>(A2,5)<\/code><\/div>\n<p>Excel MID function returns the desired number of characters from the middle of the string. We want to extract the word &#39;Easy&#39; from the text. If we want to use the MID function, the formula used will be as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">MID<\/span>(A2,8,4)<\/code><\/div>\n<p>There is an additional parameter, the second one, which is the starting location \u2013 8. As the first word is &#39;Smart&#39;, which is 5 characters long followed by a comma and a space, this parameter becomes 8.<\/p>\n<p>The RIGHT function works exactly like the LEFT function, just from the opposite direction. We are trying to extract the last word from the input sentence. Formula used is:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">RIGHT<\/span>(A2,9)<\/code><\/div>\n<p>The result is the last word &#39;Effective&#39; which is 9 characters long.<\/p>\n<p>That wraps up the LEFT function. Hope these examples gave you a good insight into how you can use the LEFT function in your worksheets. Spend a little time with these formulas, and you will champion them in no time.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The LEFT function is one of the many common and widely used text\/string functions offered by Microsoft Excel. The LEFT function enables you to extract a predetermined number of characters from a text string beginning on the left side. You just have to provide the input text or the reference cell to the function and [&hellip;]<\/p>\n","protected":false},"author":9,"featured_media":6914,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/6908"}],"collection":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/comments?post=6908"}],"version-history":[{"count":0,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/6908\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/media\/6914"}],"wp:attachment":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/media?parent=6908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/categories?post=6908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/tags?post=6908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}