LEN
Returns the number of characters in a text value.
LEN(<text>)Returns: A whole number (a scalar).
What it does
LEN counts the characters in a text value, including spaces. It is the quick test for codes that must have a fixed length, such as a 10-character employer identification number once the hyphen is included, or a 12-digit account number. It is also used to spot stray spaces or truncated values from an export.
Syntax and arguments
- text: the value to measure. A number is converted to text first, so leading zeros in a number are lost before LEN sees them.
Example
The AP team wants to flag vendor tax IDs that are not in the 10-character format used for employer identification numbers, such as "12-3456789".
Tax ID Length Issue =
IF(
LEN(Vendors[TaxID]) <> 10,
1,
0
)
A tax ID typed without the hyphen, or with a stray space, gets a 1. The flagged vendors go on a correction list before the 1099 season.
Example: a second, different use
Find text fields that carry trailing spaces, which break matches between two systems:
Padded Names = IF(LEN(Customer[CustomerName]) <> LEN(TRIM(Customer[CustomerName])), 1, 0)
If trimming changes the length, the name has extra spaces. A count of flagged rows shows how much cleanup the master data needs.
Things to watch
- LEN counts spaces. Run TRIM first when the source may contain spaces you do not want to count.
- Leading zeros in numbers are lost when the number is stored as a number. Store codes such as account numbers as text when zeros matter.
- LEN counts characters, which may not match the length in bytes for non-English text.
- A blank returns zero, so a missing code looks like a short code. Test for blank first.
- LEN is a format check, not a validity check. A correct length can still hold a wrong number.
In business terms
LEN supports data validation before a filing, a payment run or an import: account numbers of the right length, identifiers of the right format and names without trailing spaces. It is one of the simplest tests, and one of the most useful for clean vendor and customer masters.