Regex Returning extra empty Value

Question

Set Regex = New RegExp
Regex.Pattern = """[^""]*""|[^,]*"
Regex.Global = True

//I have a for loop here to loop through records

text = Cells.Item(r, 7).Value
For Each Match In Regex.Execute(text)
    count = count + 1
    Next Match

This is my Regex Code, and here is the table where I am pulling the data from,

enter image description here

When I run the code in debug mode the PCBaa count comes up as two, c3 and c4 come up as 14 and C6-c36 come up as 36, Is my regex code wrong for extracting the codes between the commas ??

Markus Szumovski · Accepted Answer · 2013-07-12 11:37:58Z

3

Ok, I have tried that myself and it seems that first off, it seems you don't reset the count value to 0 after each line. That could be intentional, but just so you know. The second thing is that the regular expression seems to work nearly fine but always gives you the double amount because it matches a zero length string at the end of each match. So for the last line (C6-C26) it machtes:

1) "C6" 2) "" 3) "C7" 4) "" ... and so on.

To be hounest, I'm a little bit surprised myself and don't exactly know why that's the case for now. But the solution is pretty easy: Since you want there to be no zero length strings in the result (so they don't get counted) you simply have to exchange the * for a + and that will tell the regular expression to match only if there's at least one character. So your regular expression string should look like:

Regex.Pattern = """[^""]+""|[^,]+"

Why you've got a count of 14 on the c3, c4 surprises me... I got a 4 which makes sence because of the double counting due to the zero length matches.

answered Jul 12, 2013 at 11:37

Markus Szumovski

1241 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bawn Over a year ago

Thanks for the help , I was confused aswell as too why the zero length was being picked up, but this has solved it many thanks :)

Collectives™ on Stack Overflow

Regex Returning extra empty Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related