3

I am trying to use the like function to distinguish between cells that begin (other than white space characters) with either 2 or 3 numerical digits followed by more white space characters, but seem to be having trouble identifying the latter. For example, for two cells, one containing

11 some text

and another containing

111 some text

I have been trying to write an if statement that is true for the first but not the second. I have tried 

if cells(i,1) like "*##[ ]*" then

and 

if cells(i,1) like "*##\s*" then

and 

if cells(i,1) like "*##[^#]*" then

following information on using regex from various sources (with the first two I was trying to identify 2 digits followed by a white space character, and the third 2 digits followed by a non-digit).

It is part of a for loop, and as in the examples above, the only numerical digits are at the beginning of the string, other than sometimes white space characters. In the first code example the if statement is true for both 2 and 3 numerical digits, and for the second and third, it is true for neither. I don't understand this given what I have read about regex and the like function.

I would greatly appreciate guidance. I expected this to be relatively simple and so I'm sure I am missing something obvious, and apologies if this is the case.

Euan Ritchie
  • 362
  • 4
  • 15

2 Answers2

7

VBA's like operator doesn't support RegEx. Instead, it has its own format. Spaces are matched using the literal value, which does not need escaping.

Input               Op      Pattern     Result
"11 Some Text"      LIKE    "## *"      True
"11Some Text"       LIKE    "## *"      False

For more see Microsoft's documentation.

If you would rather use RegEx take a look at this answer. @PortlandRunner has kindly taken the time to produce a great guide, that includes many examples.

Community
  • 1
  • 1
David Rushton
  • 4,915
  • 1
  • 17
  • 31
  • Thanks for the very quick response, and for the useful information. However I did actually try using `"## *"` and still got a match for cell content "111 some text", which is what is confusing me. Apologies for not stating that I tried this in the question but I tried a whole range of things (partly in frustration) and thought I had included the most relevant ones. I would prefer to continue using like if possible. – Euan Ritchie Apr 12 '17 at 12:17
  • I've just tested `"111 some text"` against `"## *"` and I didn't get a match. Can you try again? – David Rushton Apr 12 '17 at 12:21
  • @EuanRitchie I don't think `"## *"` should match on 111 Some Text because a space doesn't follow the two digits. The answer I posted shows that. – AlwaysData Apr 12 '17 at 12:22
  • I agree with @destination-data – AlwaysData Apr 12 '17 at 12:23
  • Thank you both, I totally agree, that was why it was confusing. However I have realised that I have been stupid. I had included an asterix at the beginning because many cells have an unspecified number of white space characters at the beginning (as shown above). But obviously this was picking up the first number as well as the white spaces. So thanks again as I noticed that quicker than I would have done otherwise, but sorry for wasting your time! @don_freem – Euan Ritchie Apr 12 '17 at 12:28
  • No need to apologise! We've all been there. [TRIM()](http://www.homeandlearn.org/trim_len_space.html) will remove leading, and trailing, spaces. Something like `TRIM(" 11 Some Text") LIKE "## *"` should work. – David Rushton Apr 12 '17 at 12:36
1

I read through the material on this MSDN site and this seemed to work for me. 

If Cells(i, 1).Value Like "## *" Then
    Debug.Print ("Match")
ElseIf Cells(i, 1).Value Like "### *" Then
    Debug.Print ("Match")
End If
AlwaysData
  • 540
  • 3
  • 8