LENformula in Google Sheets is used to count the number of characters in a given text string. It is commonly used to check the length of text inputs, or to find the length of a cell value or range.
- How to use
- Examples of using
LENformula not working?
- Similar formulas to
LEN formula with the syntax shown below, it has 1 required parameter:
- text (required):
The text string or cell range that you want to count the characters of. This parameter can be a maximum of 50,000 characters in length.
ExamplesHere are a few example use cases that explain how to use the
LENformula in Google Sheets.
Counting characters in a cell
You can use the
LEN formula to count the number of characters in a specific cell. This is useful when you need to check if the cell value is within a certain character limit.
Checking length of a text input
If you have a form or survey with a text input, you can use the
LEN formula to ensure that the user doesn't exceed a certain character limit. For example, if you only want the user to enter a maximum of 100 characters, you can use
=LEN(A1)<=100 to validate the input.
Extracting part of a text string
By combining the
LEN formula with other text formulas, you can extract specific parts of a text string. For example, you can use
=LEFT(A1,LEN(A1)-5) to extract all but the last 5 characters of a text string.
LENnot working? Here are some common mistakes people make when using the
LENGoogle Sheets Formula:
Forgetting to enclose text in quotation marks
If you forget to enclose the text string in quotation marks, the
LEN function will return an error.
Not accounting for spaces or other non-visible characters
LEN function counts all characters in a text string, including spaces and other non-visible characters. Make sure to account for these when using the function.
Using the function on non-text data
LEN function is designed to work on text strings. If you try to use it on non-text data, such as a number or a date, the function will return an error.
The following functions are similar to
LEN or are often used with it in a formula:
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
The MID formula in Google Sheets is used to extract a specific number of characters from a string, starting at a specified point. This is useful when working with large datasets and you only need a portion of the information in a cell. The formula takes in the string to extract from, the starting point, and the length of characters to extract as arguments.
LENBfunction in Google Sheets is used to count the number of bytes in a given text string. This function is useful when working with double-byte character set (DBCS) languages, such as Chinese, Japanese, or Korean, where each character is represented by two bytes. The
LENBfunction returns the number of bytes in a string, which will be twice the number of characters in the string if it contains only DBCS characters.
You can learn more about the
LEN Google Sheets function on Google Support.