Test if a string contains only whitespaces

[Robert Crandal]

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Thank you.

Robert

 

[Auric__]

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Built in? Not that I know of, but you could try this:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

....or this (which is the same thing, without wrapping it in a function):

onlyWhitespace = CBool(Len(Replace(Replace(Replace(Replace(what, " ", _
""), vbTab, ""), vbCr, ""), vbLf, "")))

For more whitespace characters, simply add another Replace.

 

[Robert Crandal]

Built in? Not that I know of, but you could try this:

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

Hi Auric..... I tried the above function but it seems to return TRUE
for cases that should be false. I ran it on the below data file:

item1 scissors
item2 glue

Each line is read into a string variable. I then passed each string
variable into "isWhitespace()" one at a time and it returned TRUE
each time. Am I doing something wrong here?

Robert

 

[Ron Rosenfeld]

Does Excel VBA have a built-in function to test if a
string variable contains ONLY whitespace characters?

For my purposes, a whitespace character can be any one of
the following: space, tab, or enter (carriage return).

I know regular expressions solves this easily, but I do
NOT want to use regular expressions for this. I'm just curious
if Excel has a built-in function to test if a string contains
1 or more of the above whitespace characters.

Thank you.

Robert

It's a bit convoluted since the VBA string comparison operator - Like - doesn't have a quantifier. So you have to construct a string of the same length as the original, consisting of repeated character classes containing your defined whitespace characters.
One way:


=============================
Option Explicit
Function WhiteSpacesOnly(s As String) As Boolean
'White space character class
Const wSpCC As String = "[ " & vbLf & vbTab & vbCr & "]"
WhiteSpacesOnly = s Like WorksheetFunction.Rept(wSpCC, Len(s))
End Function
==============================

For long strings, I don't know that this would be any faster than using VBA's regular expression engine.

 

[Ron Rosenfeld]

Function isWhitespace(ByVal what As String) As Boolean
what = Replace(what, " ", "")
what = Replace(what, vbTab, "")
what = Replace(what, vbCr, "")
what = Replace(what, vbLf, "")
isWhitespace = CBool(Len(what))
End Function

For VBA, I think you have that reversed.
False = 0

 

[Ron Rosenfeld]

For VBA, I think you have that reversed.
False = 0

Maybe "reversed" is not the rigtht word.
You might use : isWhitespace = CBool(Len(what)-1)) but that would return True for a null string also; so you'd need to check that the original "what" is not a null string, if I understand the OP's requirements.
And, unexpectedly, my UDF also returns True if s = "".

To correct for that in mine, try:
=============================
Option Explicit
Function WhiteSpacesOnly(s As String) As Boolean
'White space character class
Const wSpCC As String = "[ " & vbLf & vbTab & vbCr & "]"
WhiteSpacesOnly = (s Like WorksheetFunction.Rept(wSpCC, Len(s))) * Len(s)
End Function
============================

 

[RB Smissaert]

How about something like this:

Function StringHasOnlyWhiteSpaces(strString As String) As Boolean

Dim i As Long
Dim arrBytes() As Byte

arrBytes = strString

For i = 0 To UBound(arrBytes) Step 2
If arrBytes(i) <> 32 And _
arrBytes(i) <> 13 And _
arrBytes(i) <> 10 And _
arrBytes(i) <> 9 Then
Exit Function
End If
Next i

StringHasOnlyWhiteSpaces = True

End Function

Note that this is on the whole a lot faster than looping through the string
with Mid,
unless the string is quite long and the non-whitespace character is at the
beginning
of the string.


RBS

 

[Auric__]

Hi Auric..... I tried the above function but it seems to return TRUE
for cases that should be false. I ran it on the below data file:

item1 scissors
item2 glue

Each line is read into a string variable. I then passed each string
variable into "isWhitespace()" one at a time and it returned TRUE
each time. Am I doing something wrong here?

(This is what happens when I post while tired...)

No, Ron Rosenfeld is correct, I got the truth values reversed. The last line
should be:

isWhitespace = Not(CBool(Len(what)))

....or you can use one of the solutions Ron or RB Smissaert posted.

 

[Maurizio Borrelli]

Il giorno sabato 2 febbraio 2013 08:40:28 UTC+1, Robert Crandal ha scritto:

Maybe?...

Public Function HasOnlyWhiteSpaces(ByVal Text As String) As Boolean
With Application.WorksheetFunction
HasOnlyWhiteSpaces = Len(.Trim(.Clean(Text))) = 0
End With
End Function