[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/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”]=LEN(text)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- text<\/strong>\u00a0<\/span>\u2013 is the input string whose length the function will return.<\/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
How to use the LEN function<\/h2>\n
Obviously, that syntax is as simple and straightforward as it gets. Nevertheless, I\u2019ll show the function in action with the help of a few examples. Have a look at the screenshot below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-1-1.png” alt=”len-function-google-sheets-1″ title_text=”len-function-google-sheets-1″ align=”center” _builder_version=”4.23.1″ _module_preset=”default” 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
This function accepts direct text values within a pair of double quotes. This is shown in the first example with the formula =LEN(“Hello there!”)<\/strong>. It also accommodates a reference to the cell that has text value in it, as shown in the second row with the function =LEN(A3)<\/strong>. Please note that apart from the letters, the function also counts empty spaces and special characters.<\/p>\n
[\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light 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” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nAutomate data transfers between spreadsheets
<\/span><\/p>\nFind out how<\/a><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
LEN function over a range of cells<\/h3>\n
The LEN function is not limited to just one cell \u2013 it can be used to count the characters in a range of cells. To do this, however, you need to combine the LEN function with the SUMPRODUCT function.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-2-1.png” alt=”len-function-google-sheets-2″ title_text=”len-function-google-sheets-2″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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
I used the formula =SUMPRODUCT(LEN(A2:A6))<\/strong>, therefore I am able to count the total number of characters within that range.<\/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
Separating first and last names<\/h3>\n
Simply finding the length of a text string may not be very useful depending on your situation. The real utility of the LEN function lies when it\u2019s used in conjunction with other text functions.<\/p>\n
As a simple example, assume that you need to extract last names from a list of names. The composite names are in this format: [first name] + [empty space] + [last name]. The names cannot be separated with the LEN function only because they are not all of the same length. The only thing you know is that there is a space between the first and last names.<\/p>\n
We made use of the RIGHT, FIND, and LEN functions to achieve the following result. I encourage you to think through how this composite function is extracting the last names.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-3-1.png” alt=”len-function-google-sheets-3″ title_text=”len-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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
Use the formula =RIGHT(A2,LEN(A2)-FIND(” “,A2))<\/strong> to return the text after the space, which will always be the last name in this dataset.<\/p>\n
Essentially, the formula uses the FIND function to identify where the space is, then subtracts that from the overall length of the string in order to use the RIGHT function to fetch the text that is on the right side of the space.<\/p>\n
As a result, the combined function fetches the text on the right side of the space, which in this case, is always the last name!<\/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
Excluding spaces<\/h3>\n
You\u2019ve now seen the LEN function put to use in its basic form as well as a more complex application. But what if you want to count the number of characters without including spaces? Don\u2019t worry, there\u2019s a way to do that! In the example below, I show two different versions of excluding spaces.<\/p>\n
The first (column C) excludes \u201cadditional\u201d spaces only \u2014 extra spaces that are at the beginning or end of the string, or double spaces within the string. It still counts a single space between words. This is done using the TRIM function, which gets rid of these additional spaces. So simply type =LEN(TRIM(A2))<\/strong> to count the characters in cell A2 without worrying that there are hidden additional spaces that mess up your count.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-4-1.png” alt=”len-function-google-sheets-4″ title_text=”len-function-google-sheets-4″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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
When you don\u2019t want to count any spaces at all, even between words, use the following example.<\/p>\n
In column D, I use the SUBSTITUTE function to turn all spaces into an empty string, essentially deleting them. Then the LEN function counts up the characters of this altered string, which doesn\u2019t have any spaces for the function to count. This is done by typing =Len(SUBSTITUTE(A2,” “,””))<\/strong>.<\/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
Counting specific characters<\/h3>\n
Now that you\u2019ve been introduced to the SUBSTITUTE function, I\u2019ll use it again in a slightly more complicated way. This time, I created a function that will count the number of occurrences of a specific character.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-5-1.png” alt=”len-function-google-sheets-5″ title_text=”len-function-google-sheets-5″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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 the image above, the function is counting how many m\u2019s are in each text cell. This is done with the formula =LEN(A2:A6)-LEN(SUBSTITUTE(LOWER(A2:A6),”m”,””))<\/strong>. <\/p>\n
Essentially, it is using the same logic as the last example, where we substituted the spaces with blank text strings. In this example, we substitute the letter m with a blank text string, and the difference between the length of this altered text string and the original string is the number of m\u2019s.<\/p>\n
We also use the LOWER function to convert all the m\u2019s into lower case \u2013 otherwise any capital M\u2019s wouldn\u2019t be counted. If you only want to count upper or lower case letters, just leave out the LOWER function.<\/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
Counting words<\/h3>\n
By now you hopefully have a good grasp on how to count characters in a variety of scenarios. Now I\u2019ll show you how to count the number of words that are contained within a text string. Again, this method uses the SUBSTITUTE function. The full formula here is =LEN(TRIM(A2))-LEN(SUBSTITUTE((A2),” “,””))+1.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/LEN-function-6-1.png” alt=”len-function-google-sheets-6″ title_text=”len-function-google-sheets-6″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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 logic in this is fairly straightforward: if every word has a space in between, then count the number of spaces (and add one to represent the first word, which doesn\u2019t have a space before it) you arrive at the number of words in a string.<\/p>\n
This is again done by substituting the spaces for an empty string, then subtracting the length of that string from the length of the unaltered string. The TRIM function gets rid of any extra hidden spaces in the text.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
LEN function<\/h2>\n
That’s how you use the LEN function in Google Sheets! Looking for more spreadsheet tutorials and tips? Check out our other posts below!<\/p>\n
If you are a more advanced spreadsheet user, feel free to read and learn more about Google Apps Script in the following article:\u00a0https:\/\/www.toptal.com\/google-docs\/extending-google-sheets-app-scripts<\/a>.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
With spreadsheets, you often find yourself dealing with text in addition to numbers. Google Sheets has a multitude of text functions at your disposal that can help you manipulate the strings (a string is just a fancy word for text). The LEN function in Google Sheets is one of the most common text functions. It […]<\/p>\n","protected":false},"author":40,"featured_media":29579,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Using spreadsheets we not only can deal with numbers but also with text. We have a multitude of text formulas at our disposal that can help us manipulate the strings. The LEN<\/a> formula in Google Sheets is one of them that we use very commonly. It simply returns the length of a string that we pass to it. Other than that, we can also use this formula to dynamically extract relevant information depending on our requirements.\n
Syntax<\/h3>\nLEN(text)<\/strong>\n
- \n \t
- text<\/strong> - is the input string whose length the formula will return.<\/li>\n<\/ul>\n
Usage: LEN formula in Google Sheets<\/h3>\nObviously, that syntax is as simple and straightforward as it can get. Nevertheless, let us quickly see the formula in action with the help of a few examples. Have a look at the below snapshot that is taken off the Google Sheets application.\n\n
![\"LEN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/LEN-formula-Illustration-Frame-1.png\")
\n\nThis formula accepts direct text values within a pair of double quotes. It also accommodates a reference to the cell that has text value in it. Please note that apart from the alphabets, the formula counts empty spaces and special characters as well.\nUse case<\/h4>\nAs a standalone formula, this may not be as useful. But its real utility lies when used in conjunction with other text formulas. As a simple example, assume that we need to extract last names from a list of names. The composite names are in this format: [first name] + [empty space] + [last name]. All the names can't be imposed with a common length of the last name. Because, in reality, no name, first or last, can be of fixed length. All we know is that there is a space between the first and the last names. We will make use of the RIGHT<\/a>, FIND<\/a>, and LEN formulas to achieve the following result. We encourage you to think through how this composite formula is helping us extract the last names.\n\n
![\"LEN](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/LEN-formula-Illustration-Frame-2.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[55,39,28],"class_list":["post-9035","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\/9035","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=9035"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/9035\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/29579"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=9035"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=9035"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=9035"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- text<\/strong> - is the input string whose length the formula will return.<\/li>\n<\/ul>\n