Excel LEN Function
The Excel LEN function returns the number of characters in a specified string
Example: Excel LEN Function
|
=LEN("Exceldome")
|
Result in cell C5 (9) - returns the number of characters from the specified string which comprises only letters.
|
|
=LEN("ab 12")
|
Result in cell C6 (5) - returns the number of characters from the specified string which comprises letters, numbers and space. The LEN function capture all of these characters including the space.
|
|
=LEN("*a!#$%^&()")
|
Result in cell C7 (10) - returns the number of characters from the specified string which comprises symbols and letter. The LEN function capture all of these characters.
|
METHOD 2. Excel LEN Function using links
EXCEL
|
=LEN(B5)
|
Result in cell C5 (9) - returns the number of characters from the specified string which comprises only letters.
|
|
=LEN(B6)
|
Result in cell C6 (5) - returns the number of characters from the specified string which comprises letters, numbers and space. The LEN function capture all of these characters including the space.
|
|
=LEN(B7)
|
Result in cell C7 (10) - returns the number of characters from the specified string which comprises symbols and letter. The LEN function capture all of these characters.
|
|
=LEN(B8)
|
Result in cell C8 (5) - returns the number of characters from the specified string which comprises a date. The LEN function counts the number of characters that make up the date in numeric format. For example, date 15/03/2017 has a numeric value of 42809 which comprises five characters.
|
METHOD 3. Excel LEN function using the Excel built-in function library with hardcoded values
EXCEL
| =LEN(B6,3) Note: in this example we are populating the Text input box with a string that comprises only letters. |
 |
METHOD 4. Excel LEN function using the Excel built-in function library with links
EXCEL
| =LEN(B5) Note: in this example we are populating the Text input box with reference to a cell that comprises only letters. |
 |
Dim ws As Worksheet
ws.Range("C5") = Len("Exceldome")
ws.Range("C6") = Len("ab 12")
ws.Range("C7") = Len("*a!#$%^&()")
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named LEN.
Output Range: Select the output range by changing the cell references ("C5"), ("C6") and ("C7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: The VBA code will not recognise the date in numerical format, it will count the number of characters that make up the date. For example, if the string refers to 15/03/2017 the VBA LEN function will return a value of 10. If you are entering the date directly into the VBA code you will need to put quotation marks around the date.
METHOD 2. Excel LEN function using VBA with links
VBA
Dim ws As Worksheet
ws.Range("C5") = Len(ws.Range("B5"))
ws.Range("C6") = Len(ws.Range("B6"))
ws.Range("C7") = Len(ws.Range("B7"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named LEN.
Output Range: Select the output range by changing the cell references ("C5"), ("C6") and ("C7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: The VBA code will not recognise the date in numerical format, it will count the number of characters that make up the date. For example, if the string refers to 15/03/2017 the VBA LEN function will return a value of 10.
The Excel LEN function returns the number of characters in a specified string.
=LEN(text)
text: (Required) The string from which to extract the characters.
ADDITIONAL NOTES
Note 1: The LEN function will count the letter, numerical, date (numeric value), symbol, decimal points and space characters that form part of the string.
Note 2: If the string refers to a date the LEN function will use it's numerical value. For example, if the string refers to 15/03/2017, which has a numeric value of 42809 (five characters), the LEN function will return a value of 5.
Note 3: The VBA code will not recognise the date in numerical format, it will count the number of characters that make up the date. For example, if the string refers to 15/03/2017 the VBA LEN function will return a value of 10. If you are entering the date directly into the VBA code you will need to put quotation marks around the date.