Skip to main content

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

Author: Xiaoyang Last Modified: 2020-05-26

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?

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 Toolsets12 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...

Description


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!
Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Using LET to define variables. 
Find Last occurrence of;
=LET(strFind,"-",strFlag,"¤",FIND(strFlag,SUBSTITUTE(A2,strFind,strFlag,LEN(A2)-LEN(SUBSTITUTE(A2,strFind,""))),1))
Text to right of;
LET(strTarget,A2,strFind,"-",strFlag,"¤",RIGHT(strTarget,LEN(strTarget)-FIND(strFlag,SUBSTITUTE(strTarget,strFind,strFlag,LEN(strTarget)-LEN(SUBSTITUTE(strTarget,strFind,""))),1)))
This gives an error if strFind is not in the target. This can be solved by wrapping FIND in an IFERROR. 
LET(strTarget,A2,strFind,"-",strFlag,"¤",IFERROR(FIND(strFlag,SUBSTITUTE(strTarget,strFind,strFlag,LEN(strTarget)-LEN(SUBSTITUTE(strTarget,strFind,""))),1),0))
LET(strTarget,A2,strFind,"-",strFlag,"¤",RIGHT(strTarget,LEN(strTarget)-IFERROR(FIND(strFlag,SUBSTITUTE(strTarget,strFind,strFlag,LEN(strTarget)-LEN(SUBSTITUTE(strTarget,strFind,""))),1),0)))
This will return the existing string if strFind is not found. If this is not desired use the find separately then test for result>0.
This comment was minimized by the moderator on the site
Thank You, great work !! Best Regards Anders
This comment was minimized by the moderator on the site
Thank you for these formulas. Very helpful.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations