Excel Len VBA Function: Return the Length of a String

Excel Len VBA Function

The Excel Len function is a very useful function to use when working with strings. The function is used to return the length of a string. It is however important to remember that spaces are also considered to be character. So, be sure to use the trim function in conjunction with the len function where appropriate (refer to the example below).

The basics:

Let’s assume that you receive data which you are required to split into two separate sets of data namely the date and the rest of the text.

As luck would have it, we have thousands of records to process so performing this task manually would not be efficient. For the purpose of this example we are also assuming that the length of the date value is constant and represented by 10 characters.

We also want to write the output the adjacent columns namely column B and C.

Example code

Dim StringToProcess As String       ‘The variable that will contain the string to be evaluated

Dim A As Long                                ’The counter we will be using the process our records

For A = 1 To 10000                  ’In this example we assume the maximum amount of rows to process does not exceed 10000

StringToProcess = ActiveSheet.Cells(A, 1).Value

‘We want to process all the values in the first column of the active worksheet. For our example we have fixed the column value at 1 (so it is static) and allow the row value to change as we process the records.

ActiveSheet.Cells(A, 2).Value = Left(Trim(StringToProcess), 10)

‘Grab the first 10 characters of the string for the date. If we didn’t use the ‘trim function the output of row 2 would have been inconsistent compared to the rest of the data.

ActiveSheet.Cells(A, 3).Value = Mid(Trim(StringToProcess), 11, Len(Trim(StringToProcess)) – 10)    ‘Grab the data from the date forward.

Next

Processed data

In conclusion always try to anticipate variations in the code you have to process. In this example, our use of the trim function in conjunction with the len function ensured that the output were accurately processed.