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:
=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:
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.
- Remove First, Last X Characters Or Certain Position Characters
- 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
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!
