How to find the first, last or nth occurrence of a character in Excel?

Supposing you have the following list of text strings which including the character “-”, and now you want to get the last or nth occurrence of the character “-”, have you any good ideas to deal with this problem?


Find the last occurrence of character with formulas

Here are some formulas can help you to find the last position of a specific character, please do as follows:

1. Next to your text string, please enter or copy any one of the below formulas into a blank cell:

=SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))
=LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)="-"),ROW(INDIRECT("1:"&LEN(A2))))

2. Then drag the fill handle over to the range that you want to apply this formula, and you will get the last occurrence of the specific character “-”, see screenshot:

Note: In the above formulas, A2 indicates the cell value which you want to use. And “-” stands for the specific character you want to get its last position, you can change it to any other characters as your need.


Find the last occurrence of character with User Defined Function

To get the position of the last character “-”, you can also create User Defined Function, do as this:

1. Open your worksheet that you want to use.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications Window.

3. Then click Insert > Module, and paste the following macro in the Module window.

VBA code: find the last occurrence of character

Function LastpositionOfChar(strVal As String, strChar As String) As Long
LastpositionOfChar = InStrRev(strVal, strChar)
End Function

4. Then save and close this code, go back to the worksheet, and enter this formula =lastpositionofchar(A2,"-") into a blank cell besides your data, see screenshot:

5. And then drag the fill handle to the range you need to apply this formula, and the occurrence of last character “-” have been extracted from the text strings as following screenshot:

Note: In the above formula: A2 is the cell which contains the data you want to use, and “-” is the character you need to find its last position, you can modify them as your needing.


Find the first or nth occurrence of character with formula

To get the first or nth position of a specific charater, please apply the following formula:

1. Enter or copy the below formulas into a blank cell where you want to put the result, and press Enter key to get the first result:

=FIND(CHAR(160),SUBSTITUTE(A2,"-",CHAR(160),2))

2. Then, drag the fill handle down to the cells that you want to apply this formula, and the positions of the second "-" character have been calculated, see screenshot:

Note: In the above formulas, A2 indicates the cell value which you want to use, “-” stands for the specific character you want to get its position, the number 2 stands for the specified occurrence of the character that you want to get.


Find the first or nth occurrence of specific character with an easy feature

With Kutools for Excel’s Find where the character appears Nth in a string utility, you can get the position of the first or nth specific character quickly and easily.

Note:To apply this Find where the character appears Nth in a string, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

For example, I want to get the second occurrence of the character “-”, please do as follows:

1. Click a cell where you want to put the result.

2. Then click Kutools > Formula Helper > Formula Helper, see screenshot:

3. In the Formulas Helper dialog box:

  • Select Lookup option from the Formula Type drop down list;
  • Then choose Find where the character appears Nth in a string from the Choose a fromula list box;
  • In the Arguments input section, select the cell contains the text string you want to use, and enter the character that you want to get its position, at last, specify the number which you want to get the position of the character.

4. Then click Ok button, and then drag the fill handle down to the cells that you want to get the position to get the following result as you need.

Download and free trial Kutools for Excel Now !


More relative articles:

  • Extract All But First / Last Word In Excel
  • To extract all words from a cell but the first or the last word can help you to remove the unwanted word you need, in this case, of course, you can copy the wanted words and paste them in another cell one by one. But, this will be bored if there are multiple cell values need to be extracted except the first or last word. How could you extract all words except the first or last in Excel quickly and easily?
  • Extract Characters From Right To Left In A Cell
  • This article will talk about pulling or extracting characters from right in a cell until a space is reached to get the following result in Excel worksheet. A useful formula in this article can solve this job quickly and easily.
  • Find The Position Of The First Lowercase Letter
  • If you have a list of text strings which contain both uppercase and lowercase letters, now, you want to know the position of first lowercase letter from them in Excel worksheet. How could you get the result quickly without counting them one by one?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anders · 3 years ago
    Thank You, great work !!

    Best Regards Anders
  • To post as a guest, your comment is unpublished.
    Tom · 3 years ago
    Thank you for these formulas. Very helpful.