View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default 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.