## Find the nth occurrence of a character in Excel – 3 quick ways

Finding the nth occurrence of a specific character within a text string in Excel can be particularly useful in data analysis, where you might need to parse strings or extract information based on certain delimiters or patterns. For instance, let's find the 2nd or 3rd occurrence of the character "-" in a text string. I'll demonstrate straightforward techniques to efficiently accomplish this task.

#### Find nth occurrence of a character in text string with formula

You can craft a formula to find the nth occurrence of a character. Please do as this:

1. Enter or copy the following formula into a cell where you want to get the result:

``=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))``
Note: In the above formula:
• A2: The cell that contains the string.
• -: The character you are looking for.
• 2: The 2nd occurrence you want to find, you can change it to 3,4…as you need.

2. Then, drag the formula fill down to fill the formula to other cells, and the 2nd position of the character – are displayed at once, see screenshot:

Explanation of the formula:
• SUBSTITUTE replaces the nth occurrence of the character with a non-printable character (CHAR(1)).
• FIND searches for this non-printable character, giving the position of the nth occurrence.

#### Find nth occurrence of a character in text string with Kutools for Excel

If you're not a fan of using formula or VBA, you might consider a convenient alternative – Kutools for Excel. Within its Formula groups, you'll find a useful utility – Find where the character appears Nth in a string which quickly identifies and returns the nth position of any character in a cell.

After installing Kutools for Excel, click Kutools > Formula Helper > Formula Helper to open the Formula Helper dialog box. Click a cell where you want to put the result. Then, do as follows:

1. Select Lookup from the drop-down list of Formula Type section;
2. Choose Find where the character appears Nth in a string in Choose a formula section;
3. Select the cell which contains the string you use, then type the specified character and nth occurrence in to the textboxes in the Arguments input section;
4. At last, click OK button to get the result.
Tips: Interested in this feature, please click to download to get a free trial for 30 days.

#### Find nth occurrence of a character in text stringl with User Defined Function

In this section, we'll explore how to create and use a UDF to find the nth occurrence of a character in Excel, providing a step-by-step guide to help you optimize your data handling.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
`````` Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
'Updateby Extendoffice
Dim J As Integer
Application.Volatile
FindN=0
For J=1 To N
FindN=InStr(FindN + 1, sInputString, sFindWhat)
If FindN=0 Then Exit For
Next
End Function``````
3. Then, close the vba window. Go back to the worksheet, enter the following formula into a cell, and then drag the fill handle down to fill the formula to other cells, see screenshot:
``=FindN("-",A2,3)``

4. #### Related Articles:

• Count the number of occurrences of a word in a column
• If you have a column data which includes some duplicate values as below screenshot shown, and now what you want to do is to count the number of the occurrences of a specific word in this column. Now with this tutorial, I introduce some tricks to solve it quickly in Excel.
• Extract a nth character from a string
• In general, you may want to extract string after a specific character, but in this case, I want to extract the nth character from a string as below screenshot shown.
• Extract first/last n characters from string
• For instance, there is a list with long strings in each cell, and you want to extract the first n characters from each string only, like the first 3 characters of each string, and now you can use the following methods to solve it in Excel.

### Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |  Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |  Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
This comment was minimized by the moderator on the site
Your formula to find the Nth occurrence of a character in a string does not work. Using your example, adding 2 to the results of the imbedded find ensures a starting position for the initial find of a position beyond the 1st two c's but changing the +2 to +3 does not yield the position of the 4th "c", it simply starts the search one character deeper into the string and the result is still the 3rd "c". To find the 4th "c" the search must begin beyond the position of the 3rd "c". I look forward to your new solution.
This comment was minimized by the moderator on the site
Hello, David

Yes, as you said, the first formula does not work correctly, you can apply the second formula:
``=FIND(CHAR(1),SUBSTITUTE(A2,"c",CHAR(1),2))``

Note: The number "2" in the formula means the second "c", if you want to get the third, fourth "c", just change the number 2 to 3,4 as you need.