{"id":440818,"date":"2022-05-02T21:00:25","date_gmt":"2022-05-02T18:00:25","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-count-characters-in-a-cell-in-google-sheets\/"},"modified":"2022-05-02T21:00:25","modified_gmt":"2022-05-02T18:00:25","slug":"how-to-count-characters-in-a-cell-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/","title":{"rendered":"#How to Count Characters in a Cell in Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2dfb3254800\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" 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: #dd3333;color:#dd3333\" 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 type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2dfb3254800\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/#%E2%80%9CHow_to_Count_Characters_in_a_Cell_in_Google_Sheets%E2%80%9D\" >&#8220;How to Count Characters in a Cell in Google Sheets&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/#Count_Characters_in_a_Cell\" >Count Characters in a Cell<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/#Count_Characters_in_a_Range_of_Cells\" >Count Characters in a Range of Cells<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/#Count_Characters_Without_Extra_Spaces\" >Count Characters Without Extra Spaces<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-characters-in-a-cell-in-google-sheets\/#Count_Instances_of_Specific_Characters_in_a_Cell\" >Count Instances of Specific Characters in a Cell<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Count_Characters_in_a_Cell_in_Google_Sheets%E2%80%9D\"><\/span>&#8220;How to Count Characters in a Cell in Google Sheets&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Unlike Google Docs, Google Sheets doesn\u2019t automatically\u00a0keep track of character counts\u00a0for you. If you need to count\u00a0all or specific characters in a cell, there are a few ways to accomplish this, and we\u2019ll show you how.<\/p>\n<p>You might use Google Sheets for tracking titles of articles or essays where the count is crucial. Or maybe you want to limit the length of data others enter in your sheet and need the current counts. Whatever the case, you can use the LEN function along with options to remove extra spaces from the count or count only specific characters.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Characters_in_a_Cell\"><\/span>Count Characters in a Cell<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/761991\/how-to-count-characters-in-microsoft-excel\/\">LEN function in Google Sheets works just like it does in Microsoft Excel. It gives you the number of characters in a cell using a simple formula.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Characters in Microsoft Excel<\/em><\/strong><\/p>\n<p>The syntax for the function is <code>LEN(text)<\/code> where you can use a cell reference or actual text for the argument.<\/p>\n<p>To find the number of characters in cell A1, you would use this formula:<\/p>\n<pre>=LEN(A1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796209\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENCell-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN for a cell reference\" width=\"517\" height=\"90\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To find the number of characters in specific text, use the following formula placing the text within quotes:<\/p>\n<pre>=LEN(\"Tomorrow is another day\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796208\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENText-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN for text\" width=\"387\" height=\"95\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The important thing to know about the LEN function is that it counts each character including numbers, letters, single spaces, nonprinting characters, and punctuation.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Characters_in_a_Range_of_Cells\"><\/span>Count Characters in a Range of Cells<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>While many Google Sheets functions allow you to use a cell range as the argument, LEN isn\u2019t one of them. However, by adding the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/401826\/how-to-calculate-a-weighted-average-in-excel\/\">SUMPRODUCT function to the LEN formula, you can count characters in a cell range.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Calculate a Weighted Average in Excel<\/em><\/strong><\/p>\n<p>The SUMPRODUCT function calculates the sum of arrays, or ranges of cells. Its syntax is <code>SUMPRODUCT(array1, array2, ...)<\/code> where only the first argument is required.<\/p>\n<p>To find the count of the cell range A1 through A5, you would use the following formula:<\/p>\n<pre>=SUMPRODUCT(LEN(A1:A5))<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796210\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/SUBPRODUCTLEN-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUBPRODUCT function with LEN\" width=\"520\" height=\"200\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Characters_Without_Extra_Spaces\"><\/span>Count Characters Without Extra Spaces<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>As mentioned, the LEN function counts every character. This means that if you want to count characters in a cell that contains <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/756263\/how-to-remove-extra-spaces-in-your-google-sheets-data\/\">extra spaces, those get counted too.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Remove Extra Spaces in Your Google Sheets Data<\/em><\/strong><\/p>\n<p>For example, we have \u201d\u00a0\u00a0 How-To Geek\u00a0\u00a0 \u201d in cell A10. Using the LEN function to count the characters, the result is 17 because we have three extra spaces at the beginning and another three trailing spaces at the end.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796212\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENTrailingSpaces-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN counting extra spaces\" width=\"328\" height=\"88\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you have data in your sheet that contains unwanted spaces, you can remove them with the TRIM function. And by combining LEN with TRIM, you can get the correct character count without extra spaces.<\/p>\n<p>Keep in mind that the TRIM function only removes <em>extra<\/em> spaces, and that the LEN function does count single spaces like those between words. So using the below formula, our result is 11.<\/p>\n<pre>=LEN(TRIM(A10))<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796211\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENTrim-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN with TRIM removes extra spaces\" width=\"343\" height=\"116\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Count_Instances_of_Specific_Characters_in_a_Cell\"><\/span>Count Instances of Specific Characters in a Cell<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One more adjustment you may want to make when counting characters is to count certain characters. Maybe you want to know how many times the letter C <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ears in a cell\u2019s text string. To do this you\u2019ll employ another Google Sheets function which is <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/795796\/substitute-text-in-google-sheets\/\">SUBSTITUTE.<\/p>\n<p>The SUBSTITUTE function is normally used to replace text in a cell and its syntax is <code>SUBSTITUTE(current_text, find, new_text, occurrence)<\/code> where the first three arguments are required.<\/p>\n<p>Let\u2019s look at an example and then break down the pieces of the formula. Here, we\u2019ll see the number of times the letter C appears in cell A1.<\/p>\n<pre>=LEN(A1)-LEN(SUBSTITUTE(A1,\"C\",\"\"))<\/pre>\n<p>The formula breaks down as follows from right to left:<\/p>\n<ul>\n<li><code>SUBSTITUTE(A1,\"C\",\"\")<\/code> substitutes every C with what\u2019s in the quotes which is nothing.<\/li>\n<li><code>LEN(SUBSTITUTE(A1,\"C\",\"\")<\/code> counts the number of characters that are not the (substituted) letter C.<\/li>\n<li><code>LEN(A1)<\/code> counts the characters in cell A1.<\/li>\n<\/ul>\n<p>Finally, a minus sign divides the formulas to subtract the second LEN formula from the first giving us the result which is 3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796214\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENSUBSTITUTE-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN with SUBSTITUTE for counting specific characters\" width=\"511\" height=\"93\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>One downfall to counting specific characters with the SUBSTITUTE function is that it\u2019s case-sensitive. So if you examine our text and are wondering why the result is 3 instead of 4, this is the reason.<\/p>\n<p>To remedy this, you can add one more function to the formula and it\u2019s your choice. You can use UPPER or LOWER. The UPPER function converts a letter to uppercase and LOWER converts a letter to lowercase.<\/p>\n<p>So, to count all occurrences of the letter C in our cell, regardless of case, you would use one of the following formulas:<\/p>\n<pre>=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),\"C\",\"\"))<\/pre>\n<pre>=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),\"c\",\"\"))<\/pre>\n<p>If the text in your cell contains many uppercase letters, you might use the first formula, but if it contains mostly lowercase letters, you can use the second one. The key is to use UPPER with the uppercase letter in quotes and LOWER with the lowercase letter in quotes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-796213\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/04\/LENUPPERLOWER-GoogleSheetsCountCharacters.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"LEN using UPPER and LOWER\" width=\"523\" height=\"216\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You may not need to count characters in Google Sheets often, but when you do, you\u2019ll have this handy how-to. Be sure to bookmark it!<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Substitute Text in Google Sheets<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/796205\/character-count-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Count Characters in a Cell in Google Sheets&#8221; Unlike Google Docs, Google Sheets doesn\u2019t automatically\u00a0keep track of character counts\u00a0for you. If you need to count\u00a0all or specific characters in a cell, there are a few ways to accomplish this, and we\u2019ll show you how. You might use Google Sheets for tracking titles of&#8230;<\/p>\n","protected":false},"author":1,"featured_media":440819,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-440818","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/440818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=440818"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/440818\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/440819"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=440818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=440818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=440818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}