Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding the Nth Occurrence of a Character.
by Allen Wyatt
(last updated September 19, 2018)
Barry often finds himself wanting to identify the Nth occurrence of a character within a text string. He knows he can use the SEARCH and FIND worksheet functions for finding an initial occurrence, but is unsure how to find, say, the 3rd occurrence of the letter "B" within a text string.
Actually, the SEARCH function could be used to find the desired occurrence, in the following manner:
Notice how the SEARCHB function is used in a nested manner. The formula specifies what is being searched for (the letter "b") and the number of nesting levels indicates which occurrence within the cell you want to find. The formula returns the position of the desired character within the cell.
The problem with such a formula, of course, is that it is difficult to maintain and can quickly get unusable if you want to find, say, the seventh occurrence.
A more flexible formula would be the following:
This formula examines the value in A1. It substitutes the CHAR(1) code for the third occurrence of "B" within the cell. The FIND function then looks within the resulting string for the position where CHAR(1) occurs. If the desired occurrence does not exist, then the formula returns a #VALUE error.
If you prefer, you could create a user-defined function that will look for the Nth position of a character. The following is a very simple macro that takes three arguments: the string to be searched, the text to match, and the position desired.
Function FindN(sFindWhat As String, _ sInputString As String, N As Integer) As Integer Dim J As Integer FindN = 0 For J = 1 To N FindN = InStr(FindN + 1, sInputString, sFindWhat) If FindN = 0 Then Exit For Next End Function
The function is case sensitive in what it searches for, and it returns the position within the specified string at which the sFindWhat value occurs. If there is no occurrence at the specified instance, then the function returns a 0. The following shows how the function can be used in a worksheet:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10567) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding the Nth Occurrence of a Character.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a ...Discover More
You might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different ...Discover More
AutoFill is a great feature. It can detect patterns and adjust cell contents as you drag a selection on-screen. It ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.