Test if a string contains only whitespaces
On Sat, 2 Feb 2013 00:40:28 -0700, "Robert Crandal" wrote:
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.
|