如何使用 Excel VBA 计算单元格中特定字符的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21371817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Count the Number of a Specific Character in a Cell with Excel VBA
提问by parap
I have a number of items in cells that are separated by dashes. I'm trying to normalize the database by splitting rows so that each row contains only one entry. How do you find/count strings in Excel VBA. I know you can do values for whole cells with
我在用破折号分隔的单元格中有许多项目。我试图通过拆分行来规范化数据库，以便每一行只包含一个条目。如何在 Excel VBA 中查找/计算字符串。我知道你可以用
myVar = Application.WorksheetFunction.COUNTIF(Range("A1:Z100"),"Value")
I need to search a single cell and find out how many hyphens there are. Example
我需要搜索一个单元格并找出有多少个连字符。例子
123-456-789 = 2
9876-12 = 1
采纳答案by L42
Here's the UDFto count single string occurence in string:
这是UDF计算字符串中单个字符串出现的次数：
Option Explicit
Function COUNTTEXT(ref_value As Range, ref_string As String) As Long
Dim i As Integer, count As Integer
count = 0
If Len(ref_string) <> 1 Then COUNTTEXT = CVErr(xlErrValue): Exit Function
For i = 1 To Len(ref_value.value)
If Mid(ref_value, i, 1) = ref_string Then count = count + 1
Next
COUNTTEXT = count
End Function
Here's using Arrayformula:
这里使用Array公式：
=SUM(IF(ISERROR(SEARCH("-",MID(A1,ROW(INDIRECT(":$" & LEN(A1))),1))),0,1))
Entered using Ctrl+Shift+Enter.
Hope this helps.
使用输入Ctrl+ Shift+ Enter。
希望这可以帮助。
回答by irgnosis
Using hint from ron's functionabove I've created this formula and it worked fine :
使用上面ron 函数的提示，我创建了这个公式并且效果很好：
=LEN(A1) - LEN(SUBSTITUTE(A1, "-", ""))
回答by ron
This will count the number of hyphens in the activecell
这将计算活动单元格中的连字符数
Sub test()
a = Len(ActiveCell)
my_txt = Replace(ActiveCell, "-", "", 1, -1, vbTextCompare)
b = Len(my_txt)
numb_occur = a - b
End Sub
回答by davex
I found this answer:
我找到了这个答案：
Sub xcountCHARtestb()
'If countCHAR(RANGE("aq528"), ".") > 0 Then 'YES
If countCHAR(Selection, ".") > 0 Then 'YES
MsgBox "YES" & Space(10), vbQuestion ', "title"
Else
MsgBox "NO" & Space(10), vbQuestion ', "title"
End If
End Sub
Sub xcountCHARtesta() 'YES
MsgBox "There are " & countCHAR(Selection, "test") & " repetitions of the character string", vbQuestion 'YES
End Sub
Function countCHAR(myString As String, myCHAR As String) As Integer 'as: If countCHAR(Selection, ".") > 1 Then selection OR RANGE("aq528") '"any char string"
countCHAR = UBound(split(myString, myCHAR)) 'YES
End Function
回答by davex
Follow up to: davex, by davex.. :)
跟进：davex，作者：davex .. :)
I had been looking all over trying to find a way to test same for find text string in a formula. This answer seems to work correctly for both formulas / not & fits in a 1 liner.. (am still pretty novice at vba, let me know if any better way(s) ) thanks.
我一直在寻找一种方法来测试相同的公式中的查找文本字符串。这个答案似乎适用于两个公式/不适合&适合1个班轮..（我在vba仍然是新手，如果有更好的方法请告诉我）谢谢。
If countChar(UCase(Selection.Formula), UCase("offset")) > 0 Then 'YES (thee? answer, works for both formulas / not)
'If countChar(Selection.Formula, "OFFSET") > 0 Then 'yes
'If countChar(Cells(ActiveCell.row, Selection.Column).Formula, "OFFSET") > 0 Then 'yes
'If countChar(Cells(ActiveCell.row, "BG").Formula, "OFFSET") > 0 Then 'yes
'If countChar(UCase(Selection), UCase("OffSET")) > 0 Then 'yes but not work on formula
'If Selection.Formula Like "*offset*" Then 'no (for eq)
MsgBox "YES" & Space(15), vbQuestion
Else
MsgBox "NO" & Space(15), vbQuestion
End If
NOTE: in place of variable "BG" above, i use permanent work cells to improve use for column BG example, work cell A3 has / shows: BG:BG
注意：代替上面的变量“BG”，我使用永久工作单元来改进 BG 列示例的使用，工作单元 A3 具有/显示：BG:BG
=SUBSTITUTE(SUBSTITUTE(CELL("address",$BG3),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$BG3),"$",""),ROW(),"")
you will also need to dim the work cell, at the top / before the vba:
您还需要在 vba 的顶部/之前将工作单元调暗：
Dim A3 As String
A3 = RANGE("A3")
pardon, tried 3 times to get all of code into 1 box. really suggest putting a code stop start icon in the toolbar.
请原谅，尝试了 3 次将所有代码放入 1 个框中。真的建议在工具栏中放置一个代码停止启动图标。
回答by user3239098
This code might be of your help .. you can also use it as a UDF... :)
此代码可能对您有所帮助...您也可以将其用作 UDF... :)
Function CountHypens(rng_Src As Range) As Long
'A VARIANT FOR SPLITTING CELL CONTENTS
Dim var As Variant
On Error Resume Next
var = Split(rng_Src.Value, "-", , vbTextCompare)
If Err.Number <> 0 Then
Debug.Print "This cell does not have any hyphens."
Else
CountHypens = UBound(var)
End If
Err.Clear: On Error GoTo 0
End Function
