CODEWITHSUNDEEP
regexvbaexcel
k_schultz
k_schultz

Reputation: 111

Using regex and vba, extracting parts of data

I have an excel spreadsheet and within its contents it is formatted like -

Street Name, Street Number Street Direction(may not be present represented be an NSWE)

So it could look like John Doe Ave, 900 E or Jane Doe DR, 100

However, the people who used this spreadsheet put business names or other information that shouldn't be present

The part I'm stuck at is using regex patterns I'm not familiar with it and it confuses me

I have this variable

Dim strPattern As String: strPattern = "^(.+),\s(\d+)\s([NWSEnwse])"

So, I have this its working SLIGHTLY I wanted to know what changes I could make to this so it would include or exlude NWSEnwse, because right now it detects the address only when street direction is present

Upvotes: 1

Views: 57

Answers (1)

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

You may use this regex pattern to match it.

^(.+),\s+(\d+)(\s+[NWSEnwse])?

The ? at the end signifies that that part is optional. I also replaced \s with \s+ to account for any extra spaces that might have crept in.

Upvotes: 3

Related Questions