The LEN function returns the length (the number of characters) of a text string.
LEN(
text
)
- text: text string or name of the column that contains the text whose length you want to find out.
The LEN function returns an integer greater than or equal to zero indicating the number of characters in the text passed as an argument.
Blanks are considered characters.
If a number is passed as an argument, the function counts the characters necessary to display it, including the "-" sign (if it is a negative number) and the decimal point. Positive numbers do not include the sign in the count:
In any case, the thousands separator is not included in the count, as can be seen in the following image (same example as shown in the previous image after applying the thousands separator to the Valor column):
Similarly, if a Boolean is passed as an argument, the LEN function counts the number of letters that it is composed of (in English). The following example has added the Positivo column that contains the logical value True if the number in the Valor column is greater than or equal to zero, or False otherwise:
Positivo = IF(Table1[Valor]>=0,TRUE(),FALSE())
...and the Longitud ("Length") column returns the length of the Positivo field that contains the Booleans:
Finally, if a date is included as an argument, LEN will count 4 characters for the year, two characters to separate the three data (day, month and year) and as many characters as necessary to display the day and month (they will be always one or two characters for each data). Therefore, LEN will return a minimum of 8 (for example, for 1/1/2016) and a maximum of 10 characters (for example, for 12/31/2016), and this regardless of the format of the date, as can be seen in the following images:
In the following example, the first word is extracted from the Category column, word understood as a set of characters limited by a blank space. For this purpose, the SEARCH function is used to obtain the position of the first white space starting from the left of the text and with the LEFT function the characters are extracted up to said white space (note how a character is subtracted to extract only the previous characters to white space, that is, to not include white space in the first word).
If no white space is found, the SEARCH function includes as a fourth argument the LEN function that returns the length of the text contained in Category (that is, if no white space is found, the first word is considered to consist of the full text string). If this fourth argument is not included, the LEFT function would return an error caused by those text strings that do not include white spaces (and the error would be displayed in all the rows even if there are only some that do not include white spaces).
The Length column shows the length of the extracted word.
