How to count length of a word from a sentence and print the word(s) in the cell?

Question

I want to make a function where I extract all words with length = 2 from a sentence. For example, if the sentence is "The Cat is brown", I want the result in the cell to be "is". If there are multiple words with length = 2, I want to keep these too. I have tried MID, RIGHT, LEFT, etc. These does not work as the position of the word is not always identical.

I have no clue how to do this in VBA, any suggestions are welcome :)

Thanks

Answer 1

I have made you a UDF which should work for what you want. You use it like so:

=ExtractWords(Cell to check, Optional number of letters)

By default it will check for 2 letter words but you can specify as well as shown above.

Here is the code. Place it into a module

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

There are probably other ways to do it that may be easier or more efficient. This is just something I came up with.

Answer 2

Double Upper Case Occurrences

• In Excel you can e.g. use it like this:

  =getDUC(A1)
  =getDUC(A1," ")
  =getDUC(A1,",")
  =getDUC(A1,"-")
  

The Code

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