[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=LEFT(string, [number_of_characters])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- string<\/b> \u2013 is the input text from which the formula returns the left portion.\n
- number_of_characters<\/b> \u2013 [ OPTIONAL \u2013 1 by default ] \u2013 this is the number of characters that the formula should return from the left side of the string.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Usage: LEFT function in Google Sheets<\/h3>\n
A quick look at the syntax will reveal that you just need an input string<\/b> and the length of the substring to extract. That sounds relatively simple, but I\u2019ll give a few examples to help reinforce the concept. Please take a look at the image below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-LEFT-function-example.png” alt=”left function google sheets 1″ title_text=”1. LEFT function example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In each of these examples, the function begins at the start (left) of the text string and extracts the specified number of characters. The function itself is quite simple to use; the last example, for instance, uses the formula =LEFT(A4,B4)<\/b>.<\/p>\n
Like most other Google Sheets functions, the LEFT function can accept both direct values and references to cells that hold these values. I\u2019ve illustrated this in the examples, by typing the string<\/b> and number_of_characters<\/b> directly into the function in the first example, and then using cell references for the others.<\/p>\n
Regardless of the approach to inputting the formulas, you\u2019ll notice that the function always extracts the substring from the left side. In the first example, I have the string \u201cHello there!\u201d. The substring that is 5 characters long from the left is \u201cHello\u201d, which the formula has returned. The same logic applies to the other examples.\n<\/p>\n
[\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nOne tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n
Find out how<\/a><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using LEFT with FIND<\/h4>\n
So far I have used static numbers for the number_of_characters parameter. It certainly gets more interesting if you make this dynamic. Suppose that you have to extract the first names from a list of full names. Given that each first name varies in length, it is not possible to use a constant value for number_of_characters. All you know is that the first and last names are separated by a space character.<\/p>\n
In this situation, you can make use of the FIND formula. It returns the position at which a string is first found within a text. Consider the case of Shaun Crawford. If you count manually, the space character is in the sixth position. That is exactly what the FIND formula returns. Since you know the space is the sixth character, you also know that to extract the first name \u201cShaun\u201d, the function needs to take the first five characters of the string. By plugging in the FIND function and subtracting 1 (so that the space isn\u2019t included) you can extract the first name.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-LEFT-with-FIND.png” alt=”left function google sheets 2″ title_text=”2. LEFT with FIND” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula used here is =LEFT(A2,FIND(” “,A2)-1)<\/b>. This approach works for all the names regardless of their length, since the FIND function creates a dynamic input to the number_of_characters<\/b> parameter.<\/p>\n
The FIND function allows you to use this approach whenever your data is in a consistent format that allows you to \u201cfind\u201d a particular string each time. This could work for data that\u2019s separated by a comma, a dash, a common phrase, or any other character or text string!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using LEFT with LEN<\/h4>\n
You can also use the LEFT function to get rid of unwanted characters at the end of text strings. In this example, I again have a list of names, but they all have a period at the end. If you want to get rid of the period (and don\u2019t want to manually delete each one) you can use the LEFT function in combination with the LEN function.<\/p>\n
The LEN function returns the length of a text string; you can use it to generate the number_of_characters parameter within the LEFT function. By typing =LEFT(A2,LEN(A2)-1) you can specify the number_of_characters parameter to be \u201cthe length of the string, minus one character\u201d. This way, no matter what the length of the string is, you\u2019ll always be removing the last character. You can see that this works well \u2013 the periods have been removed!<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3.-LEFT-with-LEN.png” alt=”left function google sheets 3″ title_text=”3. LEFT with LEN” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can use this approach in a variety of situations. And if you want to remove more that one character from the end of the string, just change the -1 in the formula to whatever number you want!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
Spreadsheet applications today are extremely versatile and useful tools. While they are known primarily for handling numbers, they also provide many ways to operate with text values. In this post, you\u2019ll learn about the LEFT function in Google Sheets. This function helps extract a substring from the beginning of a specified string of text.=LEFT(string, [number_of_characters]) […]<\/p>\n","protected":false},"author":40,"featured_media":33410,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The spreadsheets applications of today are extremely versatile and useful tools. While they are known primarily for handling numbers, they also provide multiple utilities that help us operate on text values. In this post, we will be learning about the LEFT<\/a> formula in Google Sheets. This helps us to extract a substring from the beginning of a specified string.\n
Syntax<\/h3>\nLEFT(string, [number_of_characters])<\/strong>\n
- \n \t
- string<\/strong> - is the input text from which the formula returns the left portion.<\/li>\n \t
- number_of_characters<\/strong> - [ OPTIONAL - 1 by default ] - this is the number of characters that the formula should return from the left side of the string<\/strong>.<\/li>\n<\/ul>\n
Usage: LEFT formula in Google Sheets<\/h3>\nA quick look at the syntax will reveal that we will just need an input string<\/strong> and the length of the substring to extract it. That sounds relatively simple. But, let us substantiate our understanding with the help of a few examples. Please take a look at the image below.\n\n
![\"LEFT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/LEFT-formula-Illustration-Frame-1.png\")
\n\nLike most other Google Sheets formulas, the LEFT formula can accept both direct values and the references to the cells that hold these values. We've illustrated this in the examples.\n\nRegardless of our approach to the formulas, we'll notice that the formula extracts the substring from the left side. In the first example, we have the string \"Hello there!\". The substring that is 5 characters long from the left is \"Hello\", which the formula has returned. Same is the case with the next couple of examples we considered.\nUse case<\/h4>\nSo far we have used static numbers for the number_of_characters<\/strong> parameter. It certainly gets more interesting if we make this dynamic. Suppose that we have to extract the first names from a list of full names. Given that each first name varies in length, it is not possible to use a constant value for number_of_characters<\/strong>. All we know is that the first and last names are separated by a space character.\n\nIn such a situation, we can make use of the FIND<\/a> formula. It returns the position at which a string is first found within a text. Consider the case of Shaun Crawford<\/strong>. If we counted manually, the space character is in the sixth position. That is what exactly the FIND formula returned. Now that you know the working of both LEFT and FIND formulas, we encourage you to analyze how the combination is working in the examples below.\n\n
![\"LEFT](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/LEFT-formula-Illustration-Frame-2.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[55,39,28],"class_list":["post-9038","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-ben-collins","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/9038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=9038"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/9038\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/33410"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=9038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=9038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=9038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - number_of_characters<\/strong> - [ OPTIONAL - 1 by default ] - this is the number of characters that the formula should return from the left side of the string<\/strong>.<\/li>\n<\/ul>\n
- number_of_characters<\/b> \u2013 [ OPTIONAL \u2013 1 by default ] \u2013 this is the number of characters that the formula should return from the left side of the string.<\/li>\n<\/ul>\n