VBA Syntax Help Needed to Test Cells for More Than 1 Word (String)

[beartooth91]

Hello All -

Looking for some help - with the syntax - for testing each cell - in a column - for more than one word (string) entry in each cell. Not looking for specific words, just more than one word in the cell. (A valid entry is just one word/string.) Invalid entries - more than one word/string - are usually separated by a comma and space or just a space between the two. Assuming I'd use an If-Then-Else loop, possibly with Select Case. The main problem is I don't know the search string syntax to look for two strings separated by a space or two strings separated by a comma and space.....?

Thanks

 

[6StringJazzer]

If you find a cell with more than one word, do you just need to flag it as invalid? Or extract the words?

The simple way to detect this is:

If InStr(Cell, " ") > 0 Then

where Cell is a Range reference to a cell

You seem to know how to wrap a loop around this but if you need help with that part let me know. I need to know which column, and what rows the cells are in.

 

[Joe4]

Here is a little User Defined Function I created that you can call from your VBA code (or straight from the function on your sheet):

Function CountWords(str As String) As Long

    Dim c1 As Long
    Dim c2 As Long
    Dim arr1() As String
    Dim arr2() As String
   
'   Split string first on spaces
    arr1 = Split(Trim(str), " ")
'   Get count of words
    c1 = UBound(arr1) + 1
   
'   Split string first on commas
    arr2 = Split(Trim(str), ",")
'   Get count of words
    c2 = UBound(arr2) + 1
   
'   Return larger of two counts
    If c1 > c2 Then
        CountWords = c1
    Else
        CountWords = c2
    End If
   
End Function

Here is an example of it working right on the worksheet:

Here is the formula in cell B1, copied down:

=CountWords(A1)

This will count and return the number of words, regardless of whether they separated by spaces, commas, and a combination of both.

 

[Scott Huish]

You didn't say what you wanted to do with them, but this will put a Yes or No in Column B

Sub TestForMultipleWords()
Dim c As Range, s As String, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    s = Application.Trim(Replace(c.Text, ",", " "))
    If UBound(Split(s, " ")) > 0 Then c.Offset(, 1) = "Yes" Else c.Offset(, 1) = "No"
Next
End Sub

For a count of the words:

Sub TestForMultipleWords()
Dim c As Range, s As String, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    s = Application.Trim(Replace(c.Text, ",", " "))
    c.Offset(, 1) = UBound(Split(s, " "))  + 1
Next
End Sub

 

[Joe4]

And here is some VBA code that calls my UDF and loops through the values in a range to return cells where there are more than one word:

Sub Test()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If CountWords(cell.Value) > 1 Then
            MsgBox "More than one word in cell : " & cell.Address(0, 0)
        End If
    Next cell
End Sub

 

[HighAndWilder]

Hello All -

Looking for some help - with the syntax - for testing each cell - in a column - for more than one word (string) entry in each cell. Not looking for specific words, just more than one word in the cell. (A valid entry is just one word/string.) Invalid entries - more than one word/string - are usually separated by a comma and space or just a space between the two. Assuming I'd use an If-Then-Else loop, possibly with Select Case. The main problem is I don't know the search string syntax to look for two strings separated by a space or two strings separated by a comma and space.....?

Thanks

I'm not sure what you want to do with the result.

No VBA solution.

All you have to do is replace the range reference $A$2:$A$9.

Expected Sub Function.xlsm
	A	B	C	D	E	F
1	Data		Valid Entries	Words	Invalid Entries	Words
2	4e5#F9iuvs,zMWfCUTotx		WizStRxDUE	1	4e5#F9iuvs,zMWfCUTotx	2
3	4UoeJ36P28,JEz*hhAMc%		A!RNrVksn2	1	4UoeJ36P28,JEz*hhAMc%	2
4	X$bzQ0^7N9 V*OWQg3FPR		00jQ1NexYw	1	X$bzQ0^7N9 V*OWQg3FPR	2
5	e!3YbBJjug, E&*^D1!CY8, 7L^B8X@ifH, 9TG#MaDD34				e!3YbBJjug, E&*^D1!CY8, 7L^B8X@ifH, 9TG#MaDD34	4
6	WizStRxDUE				HJln0O6cxX, f5&2bUVTZF, %GuM@OTXMu, qN!4p&3r!G	4
7	A!RNrVksn2
8	HJln0O6cxX, f5&2bUVTZF, %GuM@OTXMu, qN!4p&3r!G
9	00jQ1NexYw
Sheet3

Cell Formulas
Range	Formula
C2:D4	C2	=LET(o,TRIM($A$2:$A$9),d,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(o,","," "),", "," ")," "," ")),e,SUBSTITUTE(d," ",""),c,(LEN(d)-LEN(e))+1,r,HSTACK(o,c),FILTER(r,INDEX(r,,2)=1))
E2:F6	E2	=LET(o,TRIM($A$2:$A$9),d,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(o,","," "),", "," ")," "," ")),e,SUBSTITUTE(d," ",""),c,(LEN(d)-LEN(e))+1,r,HSTACK(o,c),FILTER(r,INDEX(r,,2)>1))
Dynamic array formulas.

 

[beartooth91]

If you find a cell with more than one word, do you just need to flag it as invalid? Or extract the words?

The simple way to detect this is:

If InStr(Cell, " ") > 0 Then

where Cell is a Range reference to a cell

You seem to know how to wrap a loop around this but if you need help with that part let me know. I need to know which column, and what rows the cells are in.

This worked....Thanks. Its part of a series of valid entry checks for a particular column. If the entries are not proper; I just highlight them for review and correction.

I ended up with:

'Check for invalid ZEROSTATE and ONESTATE entries

   Entry = .Cells(i, "AS")

   If InStr(Entry, " ") > 0 Then

      .Cells(i, "AS").Interior.ColorIndex = 45

      g = g + 1

   End If

   Entry2 = .Cells(i, "AT")

   If InStr(Entry2, " ") > 0 Then

      .Cells(i, "AT").Interior.ColorIndex = 45

      g = g + 1

   End If