VBA Express : Excel - Get the position of the nth occurance of a character within a string

Excel

Get the position of the nth occurance of a character within a string

Ease of Use

Easy

Version tested with

97, 2003

Submitted by:

Ken Puls

Description:

This function will return the position of the nth occurance of a character in a string. This can be useful if, for example, you wanted to know where the 3rd 'e' is located in a text string.

Discussion:

This function was orginally built to find everything to the right of the last space in a string, and was coupled with the CountChar function ( http://www.vbaexpress.com/kb/getarticle.php?kb_id=235), and a Right formula. Using CountChar, we were able to count the number of spaces, and then using this function, find out the position of the last space. This result was then used as a condition of the Right formula.

Code:

instructions for use

			Option Compare Text 
Option Explicit 
 
Function CharPos(SearchString As String, Char As String, Instance As Long) 
     'Function purpose:  To return the position of the (first character of the )
     'nth occurance of a specific character set in a range
     
    Dim x As Integer, n As Long 
     
     'Loop through each letter in the search string
    For x = 1 To Len(SearchString) 
         'Increment the number of characters search through
        CharPos = CharPos + 1 
         
         'check if the next character(s) match the text being search for
         'and increase n if so (to count how many matches have been found
        If Mid(SearchString, x, Len(Char)) = Char Then n = n + 1 
         
         'Exit loop if instance matches number found
        If n = Instance Then Exit Function 
    Next x 
     
     'The error below will only be triggered if the function was not
     'already exited due to success
    CharPos = CVErr(xlErrValue) 
     
End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

From an existing workbook, save first.
In cell A1, type "Hello" (no quotes)
In cell B1, type =CharPos(A1,"l",2)
Verify that the result is 4

Sample File:

CharPos demo.zip 7.21KB

Approved by mdmackillop

This entry has been viewed 174 times.