Using FIND and MID in Excel VBA

This Excel VBA programming tutorial introduces two useful text processing functions provided by Visual Basic for Applications, part of the Microsoft Office suite of applications. The reader should be familiar with the following topics before proceeding:

• VBA for Excel
• Using VBA to access cells in Excel

The general principle behind using the FIND and MID functions to split a string is that it is known in advance that there are specific characters that break up the text. The example that is used in this article is based on the assumption that Excel cells have been created with multiple lines of text.

In order to access the second line of text, it is necessary to use the FIND function to locate the offset, and then the MID function to return a substring of the cell contents. This is an easy to implement, robust and elegant solution to the problem. It can also be extended to separate out multiple lines of text by using a FOR or WHILE loop.

The Excel/VBA FIND Function

The Excel FIND function is very useful, and takes the following generic form:

The character parameter can be either a numeric ASCII code (such as 10 for a linefeed), or it can be one of the VBA constants. The correct Visual Basic constant for the linefeed character (ASCII 10) is vbLf.

Note: Usually, the character would be specified in quotes (i.e. "a" or "_"), however the linefeed is non-printable. For this reason, it is necessary to use the Visual Basic CHAR function, with the correct ASCII code.

So, to search a cell for the linefeed character, one of the following can be used:

Or:

The FIND function returns the character position of the character in the cell, or an error value if it does not exist.

The Excel/VBA MID Function

The Excel MID function returns a substring of a cell value, based on the starting position and length of string to be returned. It takes the generic form:

The start parameter is the index into the cell that the string in cell to be extracted starts at, and the length parameter is the number of characters that the substring should contain.

Note : The indexes are zero based, so the first character of the cell is at position 0. Therefore, to return the whole contents, the start position should be 0, and the length can be calculated using the Excel LEN function.

So, to return the entire cell contents, the following would be used:

(The LEN function simply returns the length of the text in a cell).

Using FIND and MID Together

Obtaining the second line of a linefeed separated cell requires that the programmer:

• FINDs the first occurrence of the vbLf constant;
• Calculates the number of characters between that position and the end of the cell;
• Uses MID to return the appropriate substring.

This can be achieved in a single call, placed as a formula in any cell:

The reason the above contains a + 1 after the FIND call is to not return the linefeed. From here, it ought to be a simple task to refine the above to use any cell, and deal with more than two lines in a given cell.

VBA Help

Print Article

Email Article

View All Articles

How to subscribe to feeds