跳到主要内容

在 Excel 中查找某个字符第 n 次出现的位置 – 3 种快速方法

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
Find nth occurrence of a character in text string with Kutools for Excel
Find nth occurrence of a character in text stringl with User Defined Function

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))
备注：在上式中：
  • 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:

公式说明：
  • 替代 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 – 查找字符在字符串中第N个出现的位置 which quickly identifies and returns the nth position of any character in a cell.

安装 Kutools for Excel 后，单击“ 库工具 > 公式助手 > 公式助手 打开 公式助手 dialog box. Click a cell where you want to put the result. Then, do as follows:

  1. 选择 查找 从下拉列表中选择 公式类型 部分;
  2. 查找字符在字符串中第N个出现的位置 in 选择一个公式 部分;
  3. Select the cell which contains the string you use, then type the specified character and nth occurrence in to the textboxes in the 参数输入 部分;
  4. 最后点击 OK 按钮获取结果。
Tips: 对这个功能感兴趣，请 点击下载即可免费试用 30 天.

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. 按住 ALT + F11 键，然后打开 Microsoft Visual Basic应用程序 窗口。
  2. 点击 插页 > 模块，然后将以下宏粘贴到“模块窗口”中。 
     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. 相关文章：

    • Count the number of occurrences of a word in a column
    • 如果您有一个包含一些重复值的列数据，如下面的屏幕快照所示，那么现在要做的就是计算该列中特定单词出现的次数。 现在，通过本教程，我将介绍一些技巧以在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
    • 例如，在每个单元格中都有一个包含长字符串的列表，您只想从每个字符串中提取前n个字符，就像每个字符串的前3个字符一样，现在您可以使用以下方法在Excel中解决它。

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (28)
No ratings yet. Be the first to rate!
David Evans
David Evans
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.
Reply
0
0
skyyang
skyyang     David Evans
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.
Please try, hope it can help you!
Reply
Report
0
0
B. Sambamoorthy
B. Sambamoorthy
This comment was minimized by the moderator on the site
Find formula1 is imperfect. It will be a great help if it is removed. I have lost lot of time trying for 4th occurance of "a" in "vedanarayanan" Thanks.
Reply
0
0
Rizvi Kareem
Rizvi Kareem
This comment was minimized by the moderator on the site
To handle the exception of the formula, you can use same with if and iserro
=IF(ISERR(FIND("c",A1,FIND("c",A1)+2)),FIND("c",A1,FIND("c",A1)+0),FIND("c",A1,FIND("c",A1)+2))
Reply
0
0
Roo Payne
Roo Payne
This comment was minimized by the moderator on the site
"Substitute" lets you find the Nth occurrence of a term, so combine it with "find" as follows (where 5=5th occurrence):

=FIND(CHAR(1),SUBSTITUTE(A1,"c",CHAR(1),5))
Reply
0
0
David Evans
David Evans     Roo Payne
This comment was minimized by the moderator on the site
Excellent! I am glad I scrolled up to review other comments after posting my own.
Reply
0
0
SysAdminBen
SysAdminBen     Roo Payne
This comment was minimized by the moderator on the site
This is the correct answer! please replace this whole article with this one line of code.....
Reply
0
0
AnnihilatorDJ
AnnihilatorDJ
This comment was minimized by the moderator on the site
The first formula is completely useless and wrong but it works just correct for this example as the first and second "c"s are adjacent. The correct formula is:


For 2nd occurence -
=FIND("c",A1,FIND("c",A1)+1)

For 3rd occurence -
=FIND("c",A1,FIND("c",A1,FIND("c",A1)+1)+1) and so on...
Reply
Report
0
0
nmcfarland5@gmail.com
    AnnihilatorDJ
This comment was minimized by the moderator on the site
Absolutely agree. The +2, or +3 etc.....entirely depends on the spacing between the n and (n+1) occurences of "c". Thus you would have to manually adjust for every cell if you were trying to copy this formula down a column for instance. Essentially the same as manually counting.


Ex. Let's try to find the 4th occurrence of "c" in cell A1 using the provided formula. Per the article we should use +3 in our nested FIND function to find the 4th occurrence.


Text in Cell A1:

"cab car abc cole cube"

Manually counting, the 4th occurrence is the "c" in cole in the character position of 13.


=FIND("c",A1,FIND("c",A1)+3)

The nested function, FIND("c",A1) will find the first occurrence of "c" in position 1.

Therefore,

FIND("c",A1)+3 = 1+3 = 4

Plugging that back into our formula,

=FIND("c",A1,FIND("c",A1)+3) = FIND("c",A1,1+3) = FIND("c",A1,4)

This new formula will look for the first occurrence of "c" beginning from character position 4. The 4th character in the string is the space just after cab.

Therefore, the formula will find the "c" in car and return the value of 5 for our formula. As we know from manually counting we are looking for 13 as the output.
Reply
Report
0
0
Sunny
Sunny     AnnihilatorDJ
This comment was minimized by the moderator on the site
The firs formula works correct in my version, in the note, it has explained how to find the third or forth or nth "c".

Note: You can change the 2 in the formula based on your needs. For example, if you want to find the fourth position of "c", you can change the 2 to 3. And if you want to find the first position of "c", you shuold change 2 to 0.
Reply
Report
0
0
AnnihilatorDJ
AnnihilatorDJ     Sunny
This comment was minimized by the moderator on the site
Try to find out the position of the second c with the first sentence of my above comment as the string using the first formula. You'll get to know how correct it is!
Reply
Report
0
0
n.k.mandadapu@gmail.com
This comment was minimized by the moderator on the site
Hello all,

I have a text in an Excel cell " BY TRANSFER-NEFT*HDFC0000001*N08745987123546*J0032331*KUMAR--"

I need a function to extract just "N08745987123546"

Thanks
Reply
Report
0
0
Sunny
Sunny    
This comment was minimized by the moderator on the site
Hello, do you want to extract the numbers after No and the number of numbers are fixed? If so, you can use Kutools for Exccel's Extract text function， type No???？to extract.
View Attachments (1 / 5)
Reply
Report
0
0
KUMAR
KUMAR
This comment was minimized by the moderator on the site
HI ALL,
I have a text in an Excel cell " BY TRANSFER-NEFT*HDHC0065431*N053112345624801*K0038331*krishna--"
I need a function to extract just "N053112345624801"
Reply
0
0
kamil
kamil
This comment was minimized by the moderator on the site
Formula 1 doesn't work on fourth, fifth, sixth etc. so (+3,4,5) will always show third position of letter "c" which is 19 second formula is working though.
Reply
0
0
Philip Oyetunde
Philip Oyetunde
This comment was minimized by the moderator on the site
Hello all, I have a text in an Excel cell "23 floyd lane, longville,KN 14564" I need a function to extract just "longville" Thanks
Reply
0
0
Rukt
Rukt     Philip Oyetunde
This comment was minimized by the moderator on the site
If you must use a function, try: =TRIM(MID(A1,SEARCH(",",A1)+1,SEARCH("!@#",SUBSTITUTE(A1,",","!@#",2))-SEARCH(",",A1)-1)) Otherwise, you might consider Data > Text to Columns > Delimited (using a comma as the separator).
Reply
0
0
jr
jr
This comment was minimized by the moderator on the site
Try this: =INDEX(SMALL(IF(MID(A1,MATCH(ROW(INDIRECT(1&":"&LEN(A1))),ROW(INDIRECT(1&":"&LEN(A1))),0),1)="c",MATCH(ROW(INDIRECT(1&":"&LEN(A1))),ROW(INDIRECT(1&":"&LEN(A1))),0),""),MATCH(ROW(INDIRECT(1&":"&LEN(A1)-LEN(SUBSTITUTE(A1,"c","")))),ROW(INDIRECT(1&":"&LEN(A1)-LEN(SUBSTITUTE(A1,"c","")))),0)),2) where "c" is the character required and 2 is the position. Note this is an array formula.
Reply
0
0
jr
jr     jr
This comment was minimized by the moderator on the site
this is simpler: =INDEX(SMALL(IF(MID(A1,ROW(INDIRECT(1&":"&LEN(A1))),1)="c",ROW(INDIRECT(1&":"&LEN(A1))),""),ROW(INDIRECT(1&":"&LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))))),2)
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations