我已经为您创建了一个UDF,它应该可以满足您的需求。您可以这样使用它: =ExtractWords(Cell to check, Optional number of letters) 默认情况下,它将检查2个字母的单词,但您可以指定以及如上所示。 下面是代码。将其放入模块中
Function ExtractWords(Cell As Range, Optional NumOfLetters As Integer)
Dim r As String, i As Long, CurrentString As String, FullString As String, m As String
If NumOfLetters = 0 Then NumOfLetters = 2
r = Cell.Value
For i = 1 To Len(r)
m = Mid(r, i, 1)
If Asc(UCase(m)) >= 65 And Asc(UCase(m)) <= 90 Or m = "-" Or m = "'" Then 'Accepts hyphen or single quote as part of the word
CurrentString = CurrentString & m
If i = Len(r) Then GoTo CheckLastWord
Else
CheckLastWord:
If Len(CurrentString) = NumOfLetters Then
If FullString = "" Then
FullString = CurrentString
Else
FullString = FullString & " " & CurrentString 'Change space if want another delimiter
End If
End If
CurrentString = ""
End If
Next i
If FullString = "" Then
ExtractWords = "N/A" 'If no words are found to contain the length required
Else
ExtractWords = FullString
End If
End Function
Option Explicit
' In Excel:
Function getDUC( _
ByVal s As String, _
Optional ByVal Delimiter As String = ", ") _
As String
Dim arr As Variant
arr = DoubleUCaseToArray(s)
getDUC = Join(arr, Delimiter)
End Function
' In VBA:
Sub testDoubleUCaseToArray()
Dim CCodes As Variant: CCodes = Array("US,UKUs", "UkUS,UK", "kUSUKsUK")
Dim arr As Variant
Dim n As Long
For n = LBound(CCodes) To UBound(CCodes)
arr = DoubleUCaseToArray(CCodes(n))
Debug.Print Join(arr, ",")
Next n
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose: From a specified string, returns all unique double upper case
' occurrences in a 1D (zero-based) array.
' Remarks: From the string 'USUk' it returns only 'US' (not `SU`).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DoubleUCaseToArray( _
ByVal s As String) _
As Variant
If Len(s) > 1 Then
With CreateObject("Scripting.Dictionary")
Dim cFirst As String * 1
Dim cSecond As String * 1
Dim n As Long
For n = 1 To Len(s) - 1
cFirst = Mid(s, n, 1)
If cFirst Like "[A-Z]" Then
cSecond = Mid(s, n + 1, 1)
If cSecond Like "[A-Z]" Then
.Item(cFirst & cSecond) = Empty
End If
n = n + 1
End If
Next n
If .Count > 0 Then
DoubleUCaseToArray = .Keys
End If
End With
End If
End Function
2条答案
按热度按时间olhwl3o21#
我已经为您创建了一个UDF,它应该可以满足您的需求。您可以这样使用它:
=ExtractWords(Cell to check, Optional number of letters)
默认情况下,它将检查2个字母的单词,但您可以指定以及如上所示。
下面是代码。将其放入模块中
可能还有其他的方法可能更容易或更有效。这只是我想出的办法。
mjqavswn2#
双大写出现次数
"法典"