--- title: "LEN function in Google Sheets: count characters in a cell" canonical: "https://www.owox.com/blog/articles/len-function-google-sheets" updated: "2026-10-05" --- # LEN function in Google Sheets: count characters in a cell LEN returns the number of characters in a text, counting letters, digits, spaces and punctuation. This guide covers the syntax, what LEN counts for numbers and dates, how to check a character limit, count the characters of a whole range, count words or the occurrences of a character, and how LEN works with LEFT, RIGHT, MID and TRIM. [Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 7 min read ![LEN function in Google Sheets: count characters in a cell](https://cdn.owox.ai/www/webflow/6a1435843129f7f9b0519889_USE-LEFT-RIGHT-AND-MID-FUNCTIONS-1.png/public) LEN returns the number of characters in a cell: > `=LEN(B3)` With “James Miller” in B3 the result is 12: eleven letters and one space. Every character counts, including spaces, punctuation and characters you cannot see. ## LEN syntax > `=LEN(text)` * **text:** the text, or the cell that holds it. > `=LEN(B3)` ![Google Sheets with employee names in column B and the formula =LEN(B3) returning 12 for James Miller, 13 for Emily Johnson and so on](https://cdn.owox.ai/www/webflow/67a0a6bfa2ac663bb76d5f81_61935.png/public) ## What LEN counts The results below are from our test sheet. | Formula | Result | Why | | --- | --- | --- | | `=LEN("Hello world")` | 11 | The space counts | | `=LEN(" a ")` | 3 | Leading and trailing spaces count | | `=LEN("a" & CHAR(10) & "b")` | 3 | A line break is one character | | An empty cell | 0 | Nothing to count | | A cell with the number 1500 | 4 | The digits of the value | | `=LEN(1500.50)` | 6 | The value is 1500.5; the format is not counted | | `=LEN(1/3)` | 12 | The value as text, 0.3333333333 | | A cell with the date 1/5/2023 | 8 | The date as the cell displays it | | `=LEN(TRUE)` | 4 | The word TRUE | | `=LEN("😀")` | 2 | Some emoji count as two characters | Three things to remember from the table: * **For a number, LEN counts the value, not the format.** A currency sign, thousands separators and trailing zeros added by formatting are not part of it. To count what is shown, format first: `=LEN(TEXT(D3, "#,##0.00"))`. * **For a date, LEN counted the displayed text** in our test, so the result changes with the date format. Use [TEXT](https://www.owox.com/blog/articles/text-function-google-sheets) with an explicit format to get a stable length. * **Invisible characters count.** If two cells look the same and LEN differs, one of them has a trailing space, a non-breaking space or a line break. See [TRIM and CLEAN](https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets). ![Google Sheets with salaries in column C and the formula =LEN(C3) returning the number of digits: 5 for 55000, 6 for 198000, 4 for 6000](https://cdn.owox.ai/www/webflow/67a0a6bf3c1d4495b0832681_61945.png/public) ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'll use your actual use case ## Check a character limit Ad headlines, meta titles, SMS texts and product names have length limits. A column with LEN shows the length; a comparison turns it into a check: > `=LEN(C3) <= 100` > `=IF(LEN(C3) > 100, "Too long by " & LEN(C3) - 100, "OK")` The article’s example adds a test that the cell holds text at all: > `=AND(LEN(C3) <= 100, ISTEXT(C3))` ![Google Sheets with employee descriptions and the formula =AND(LEN(C3) <= 100, ISTEXT(C3)) returning FALSE for the one description that is longer than 100 characters](https://cdn.owox.ai/www/webflow/67a0a6bfcf10245d4c9f2f8a_61987.png/public) The same comparison works in two other places: * **Conditional formatting.** Format > Conditional formatting > Custom formula is `=LEN(C3) > 100` colors the cells that are over the limit. * **Data validation.** A custom formula `=LEN(C3) <= 100` rejects longer entries or warns about them. See [data validation](https://www.owox.com/blog/articles/data-validation-google-sheets). ## Count the characters of a range LEN takes one value. Given a range, it returned #VALUE! in our test. For the total number of characters in a range, wrap it in SUMPRODUCT: > `=SUMPRODUCT(LEN(B3:B7))` ![Google Sheets with five employee names in B3:B7 and the formula =SUMPRODUCT(LEN(B3:B7)) returning 65](https://cdn.owox.ai/www/webflow/67a0a6be697fbbed0b1ee2d9_61979.png/public) `=SUM(ARRAYFORMULA(LEN(B3:B7)))` returns the same. For the length of each row from one formula: `=ARRAYFORMULA(LEN(B3:B7))`. The longest entry of a column: `=MAX(ARRAYFORMULA(LEN(B3:B7)))`. ## Count how often a character occurs Remove the character with [SUBSTITUTE](https://www.owox.com/blog/articles/substitute-function-google-sheets) and compare the lengths: > `=LEN(C3) - LEN(SUBSTITUTE(C3, "e", ""))` ![Google Sheets with descriptions in column C and the formula =LEN(C3) - LEN(SUBSTITUTE(C3, “e”, “”)) returning how many times the letter e occurs in each](https://cdn.owox.ai/www/webflow/67a0a6bfc29135dd32a0ff7d_61971.png/public) SUBSTITUTE is case-sensitive, so this counts “e” and not “E”. For a text of several characters, divide by its length: `=(LEN(C3) - LEN(SUBSTITUTE(C3, "ab", ""))) / LEN("ab")`. ### Count words Words are separated by spaces, so the number of words is the number of spaces plus one, after TRIM has removed the extra ones: > `=LEN(TRIM(C3)) - LEN(SUBSTITUTE(TRIM(C3), " ", "")) + 1` On “a b”, with two spaces in the middle, the formula returned 2. On an empty cell it returns 1, so guard it: `=IF(C3 = "", 0, ...)`. ## LEN with other text functions ### With TRIM: the length without stray spaces > `=LEN(TRIM(C3))` ![Google Sheets with descriptions and the formula =LEN(TRIM(C3)) returning the length of each text after extra spaces are removed](https://cdn.owox.ai/www/webflow/67a0a6bfa3051234fc1e180b_61975.png/public) `=LEN(C3) - LEN(TRIM(C3))` is a quick test for stray spaces: any result above 0 means the cell has some. ### With LEFT, RIGHT and MID: cut a text of changing length LEN gives the total length, from which a fixed part is subtracted: > `=LEFT(C3, LEN(C3) - 3)` returns everything but the last three characters: “EMP456” from “EMP456-JM”. > `=RIGHT(C3, LEN(C3) - 7)` returns everything after the first seven characters: “JM”. > `=MID(C3, 4, LEN(C3) - 6)` returns the middle, without three characters at each end: “456”. ![Google Sheets with employee IDs such as EMP456-JM and three columns that use LEN with LEFT, RIGHT and MID to return EMP456, JM and 456](https://cdn.owox.ai/www/webflow/67a0a6bf853e979389c8bcb4_61953.png/public) See [LEFT, RIGHT and MID](https://www.owox.com/blog/articles/left-right-mid-functions-google-sheets). ### With IF: act only on filled cells `LEN(cell) > 0` is a test for “not empty” that also treats a formula returning an empty text as empty: > `=ARRAYFORMULA(IF((LEN(B3:B7) * LEN(C3:C7)) > 0, B3:B7 & " " & C3:C7, ""))` ![Google Sheets with names and employee IDs, two IDs missing, and a formula with ARRAYFORMULA, IF and LEN that joins name and ID only in the rows where both are filled](https://cdn.owox.ai/www/webflow/67a0a6bf70a1ff62668a92eb_61983.png/public) Multiplying the two lengths gives 0 as soon as one of the cells is empty. See [array formulas](https://www.owox.com/blog/articles/array-formulas-google-sheets). ## Problems and fixes **⚠️ Problem:** LEN returns more than the visible characters. ✅ **Solution:** Spaces at the ends, non-breaking spaces or line breaks. Compare with `=LEN(TRIM(CLEAN(C3)))`. **⚠️ Problem:** #VALUE! with a range. ✅ **Solution:** Wrap LEN in ARRAYFORMULA or SUMPRODUCT. **⚠️ Problem:** The length of a number differs from what the cell shows. ✅ **Solution:** LEN counts the stored value. Use TEXT with the display format. **⚠️ Problem:** A cell that looks empty returns 1 or more. ✅ **Solution:** It contains a space or another invisible character. Clear the cell, or clean the column with TRIM. **⚠️ Problem:** A text with emoji is counted as too long. ✅ **Solution:** Some emoji and symbols count as two characters. Allow for that when checking limits by hand. ## Related Google Sheets functions * [LEFT, RIGHT and MID](https://www.owox.com/blog/articles/left-right-mid-functions-google-sheets): Parts of a text by position. * [TRIM, CLEAN and T](https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets): Remove spaces and hidden characters. * [SUBSTITUTE](https://www.owox.com/blog/articles/substitute-function-google-sheets): Replace or remove text. * [FIND and SEARCH](https://www.owox.com/blog/articles/find-and-search-functions-google-sheets): The position of a text inside another. * [TEXT](https://www.owox.com/blog/articles/text-function-google-sheets): Numbers and dates as formatted text. * [REPT](https://www.owox.com/blog/articles/rept-function-google-sheets): Repeat a text, for padding to a fixed length. ## Length checks catch problems that started upstream A column of LEN checks usually exists because the text came in untidy: names with trailing spaces, IDs of varying length, titles over a limit. The check finds them in this copy of the data, and the next export brings them back. The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas. ![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/len-function-google-sheets/screens/1.png/public) What changes for the person who maintains the sheet: * **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it. * **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report. * **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list. The extension does not write or change the analyst’s SQL. When IDs and names are normalized once in that SQL, the rows arrive in one shape, and LEN is left for checking what people type into the sheet. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) describes both ways data reaches a sheet. ## Your New Normal Turn your data into decisions. Governed data marts give you the clean foundation ML needs to actually work. * No AI hallucinations * Analyst-governed definitions * Every number traces to SQL [Get started free](https://www.owox.com/app-signup) ## Who wrote this ![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public) [Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks. [LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko) [Google Sheets Tips](/blog/topics/google-sheets-tips) ## Related articles [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets) [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](/blog/articles/query-with-concatenate-google-sheets) [Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](/blog/articles/query-with-importrange-google-sheets) [See all articles →](/blog/articles) ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md - [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md - [TEXT](https://www.owox.com/blog/articles/text-function-google-sheets) — /blog/articles/text-function-google-sheets.md - [TRIM and CLEAN](https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets) — /blog/articles/trim-clean-t-functions-google-sheets.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [data validation](https://www.owox.com/blog/articles/data-validation-google-sheets) — /blog/articles/data-validation-google-sheets.md - [SUBSTITUTE](https://www.owox.com/blog/articles/substitute-function-google-sheets) — /blog/articles/substitute-function-google-sheets.md - [LEFT, RIGHT and MID](https://www.owox.com/blog/articles/left-right-mid-functions-google-sheets) — /blog/articles/left-right-mid-functions-google-sheets.md - [array formulas](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md - [FIND and SEARCH](https://www.owox.com/blog/articles/find-and-search-functions-google-sheets) — /blog/articles/find-and-search-functions-google-sheets.md - [REPT](https://www.owox.com/blog/articles/rept-function-google-sheets) — /blog/articles/rept-function-google-sheets.md - [OWOX Data Marts](https://www.owox.com/app-signup) - [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md - [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md - [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](https://www.owox.com/blog/articles/query-with-concatenate-google-sheets) — /blog/articles/query-with-concatenate-google-sheets.md - [Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](https://www.owox.com/blog/articles/query-with-importrange-google-sheets) — /blog/articles/query-with-importrange-google-sheets.md - [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md