Help with Regular Expression pattern

[AlexanderBB]

Hi, I've managed to identify a string which starts with a Capitol A-Z followed by a number [0-9] and then a period and space.

However the number may be over 10 (so more than one character) and in some case the period and space missing. This may mean
two checks are needed but I can't get either working properly.

Have Googled a bit but can't find a good pattern tutorial for beginners for VBA RE... if you know of one I'd appreciate a link.

Many thanks, ABB

 

[pgc01]

I'm just trying (unsuccessfully) to work out how to extract anything with a leading 2 spaces, and an ending two spaces

Hi

Sorry, not enough. One thing you'll notice with regex's is that you need an exact specification.

- any characters, for ex. a sequence of 5 or more spaces ?

Consider the string between the first and last 2 spaces
- must it start with a character different from space?
- must it end with a character different from space?
- can it have spaces, for ex. "This is an example" if preceded and followed by 2 spaces would be a match?
- can it include sequences of 2 or more spaces?

 

[pgc01]

Hi Richard

Maybe I'm being too picky (go figure ), but, just to be clear for Alex, \s and \S are not related just to the usual ascii space character, they are related to some other characters too, usually known as "white space" characters.

For ex.:

Sub Test()
Dim regex As Object, regexMatches As Object
Dim sWPArr(1 To 5) As String, s As String

sWPArr(1) = " "
sWPArr(2) = Chr(9) ' HT, Horizontal Tab
sWPArr(3) = Chr(&HA) ' LF, Line feed
sWPArr(4) = Chr(&HB) 'VT, Vertical Tab
sWPArr(5) = Chr(&HC) 'FF, Form feed
s = Join(sWPArr, "")

Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "\s"
    .Global = True
    Set regexMatches = .Execute(s)
    Debug.Print regexMatches.Count
End With
End Sub

This means that the \S in your pattern does not match any of these characters. To match a non ascii space character I'd use the character class [^ ].

 

[Richard Schollar]

Wise words See, I said you'd suggest something slicker!

 

[AlexanderBB]

Hi pgc, Richard.

Battling on here but coming up with more questions than answers. I see what Richard meant and have been trying similar things.
For example

.Pattern = "\ \ *\ \ "

The * can't mean everything because it's not matching 2 spaces -anything- 2 spaces.

Also

.Pattern = "^[.A-Z][.1-9]" or .Pattern = "^[A-Z][1-9]"

is not finding a string starting with say A1 or C9

But .Pattern = "^[A-Z]" is finding a match starting with A or B etc.

Maybe I should explain the whole thing I'm trying to do? I have a string like

A1. Marvin Gaye – I Heard It Through The Grapevine (Barrett Strong, Norman Whitfield) 03:11
or
1. Marvin Gaye – I Heard It Through The Grapevine (Barrett Strong, Norman Whitfield) 03:11

I want first to establish it starts with number, or letter-number then full stop and space.
Then remove any digits at the end (if present).

That bits done. Although I need 2 tests for letter or number at the start. (And the number may be > 9, i.e. 2 digits...)
Then I want to remove anything that's bracketed, but must be the last bracketed item if more than one,

I've been achieving this with some messy and inefficient code and thought RegEx might be a better way.
Richard I've the add-on installed to Excel 2007 but can't see how to get it going.

 

[MARK858]

Richard I've the add-on installed to Excel 2007 but can't see how to get it going

Alexander, see if the link below helps

http://www.mrexcel.com/forum/general-excel-discussion-other-questions/613178-regular-expression-pattern-add.html

 

[pgc01]

The * is a quantifier, it matches 0 or more of the value that immediately precede it.
In the case of "\ \ *\ \ ", the * is preceded by a space and so "\ *" means 0 or more spaces.

.Pattern = "^[.A-Z][.1-9]"

is not finding a string starting with say A1 or C9

A1. Marvin Gaye – I Heard It Through The Grapevine (Barrett Strong, Norman Whitfield) 03:11

Yes it does. If you try this pattern with this string it will match the A1 in the string.

Maybe you have some typo in your code. Try again.