The LEN Function in excel is used to return the number of characters in a given text string. The function counts the number of characters in text including spaces and punctuations, and symbols. It may seem like a pointless function, but it is a very useful tool that can be used in a variety of ways ranging from simple data calculations to more complex calculations. In today’s post, we would walk you through all you need to know about the LEN Function and how we can incorporate this function in financial modeling. Let’s see the Syntax and argument below, then we would treat some examples in the subsequent sections.
Syntax & Argument
The LEN Function syntax is as follows:
=LEN(text)
The LEN Function has only one argument which is:
- text: This is the text or value for which you want to calculate the number of characters. The text argument could be hardcoded or selected from a cell. Kindly note however that if you are applying this function to hardcoded text within the formular, you would need to enclose the text in double quotes.
Usage Notes
The LEN Function is a very straightforward function with only a few things to note:
- The LEN Function can only take one argument
- The LEN Function considers spaces,symbols and punctuations when counting the number of characters in a text or cell containing text
- If text is empty or contains an empty string (” “), the LEN Function returns zero (0)
- The LEN Function would also count numbers. You only need to select the cell containing number or write the number (without double quotes)
Now let’s look at some examples of the LEN Function
Excel LEN Function Examples
As already established in the syntax and argument section, the LEN function can be used with hardcoded numbers inputted within the formula.
If I include a hyphen in between the words and a space after, they are also counted, and our character count goes up to 11.
The LEN Function works with numbers and you dont need to enclose the numbers in double quotation.
=LEN(65400) // Returns 5
=LEN(198650) // Returns 6
Using the LEN Function in a Financial Modelling Scenario
In this example, we would share an example of how we were able to utilize this formula in a financial modeling scenario. In our example today, we are looking at a model for calculating depreciation of assets on a straight-line basis according to their useful life. Our focus is however not on the depreciation itself but on the minute detail for our input section. How it works is that when we commit to the purchase of an asset, the purchase value is first moved to Construction in Progress (CIP) and after the vendor completes the manufacture and delivery of the asset, it is moved to the Fixed Asset Register (FAR).
Typically, due to the nature of the business and complexities involved in manufacturing our core we have an average completion period between 3 – 6 months. Nevertheless, we do not allow vendors more than 9months for the manufacture and delivery of asset purchases. Now we don’t want to define the number of months as we want the model to be flexible for scenario planning and stress testing, however we want to indicate that the maximum allowable period is 9 months. What better formula to use than the LEN formula as you will notice that 0 – 9 have one thing in common, they are all just 1 character long.
To begin, we need to create a formula in our movement to FAR section that would automatically pick the purchase value the number of months back that we specify (i.e if it takes 3 months to move to FAR, then January purchase should move to FAR by April.) Our formula to achieve this would be a combination of IF Statement, OFFSET FUNCTION and ISNUMBER Function.
Now copy and paste that formula to the other cells.
The next step is to bring up the data validation box and select the custom option. You can bring up the Data Validation box by going to Data tab > Data Tools Group > Data Validation
In the data validation box, type in the formula (be sure to adjust depending on the exact input cell) and click on Error Alert to provide instructions to the user.
Type in the instructions in the error message box and click on OK.
Now let us test a 2-digit number to see what happens:
If we input a number that is less than or equal to 9, the error disappears, and our mini model returns the correct values.
Because we inputted number 8 in Cell C4, our January purchase would be moved to FAR in September.
