1

I'm new to VBA and I need to get the value of a RegEx match and copy its value into another cell.

The string I need to match against will always be in cell E1 and will always follow this type of pattern:

CompanyName/12345 Country Product name (Optional subname) Number CUR 123456

I need everything inbetween the 5 and 6 digit numbers, so the following regex gets what I need:

\s\w.*\s

(Space, one or more word characters, space)

I just don't know how to get the VALUE of this match. I see a lot of RegEx.Replace methods on SO but I can't seem to find something that gets me the match value.

Here is my code (using RegEx.Replace):

Sub GetProdID()
    Dim RegEx As Object
    Dim myCell As Range
    Set RegEx = CreateObject("VBScript.RegExp")
    On Error Resume Next
    RegEx.Pattern = "\s\w.*\s"

    ActiveWorkseet.Range("E1").Value = RegEx.Replace(ActiveWorkseet.Range("E1").Value, "")
    Next
End Sub

How do I just get the value of the match? And how would I do this if the string I'm matching is always in one cell?

Update: The link provided in the comments led me to the right solution. Here is the code edits, thanks to Wiktor Stribiżew for help

Sub GetProdID()
    Dim myCell As Range
    Dim Prod As String
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "\s\w.*\s"

    Set matches = regEx.Execute(Range("E1"))
    Prod = Trim(matches(0))
    Range("F1").Value = Prod
End Sub
Community
  • 1
  • 1
Tensigh
  • 1,030
  • 5
  • 22
  • 44
  • Do you want to get everything between the first and last spaces? `Country Product name (Optional subname) Number CUR`? That is what your regex does now, also ensuring there is 1 word char after the first whitespace matched. See [your regex demo](https://regex101.com/r/9o8VFq/1). Your requirements are somewhat different since you say you need to get the substring between a 5- and 6-digit number. What are the *actual* requirements? – Wiktor Stribiżew Sep 15 '17 at 07:32
  • Ok, you can figure that out yourself. Your answer is [here](https://stackoverflow.com/a/43128681/3832970). – Wiktor Stribiżew Sep 15 '17 at 07:37
  • Thank you for the follow up. I would need Everything within those spaces. So I would need "Country Product name (Optional subname) Number CUR". Basically, I need to strip out everything in the middle of the 5 digit and 6 digit numbers. – Tensigh Sep 15 '17 at 07:38
  • What you say and what you try is different. So, if you just need to know how to obtain the match value, see [this answer](https://stackoverflow.com/a/43128681/3832970). Then `Trim()` the value obtained and you are done. – Wiktor Stribiżew Sep 15 '17 at 07:40
  • The problem with these examples is they always se the Regex as `Dim regEx As New RegExp`, but I get an error on `As New RegExp`. This doesn't work. – Tensigh Sep 15 '17 at 08:31
  • Did you have a look at the answer I linked *twice*? It is illustrated there, how to add a reference to the Regex library. Besides, you do not need that if you use `Set RegEx = CreateObject("VBScript.RegExp")`. You just need `If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If` – Wiktor Stribiżew Sep 15 '17 at 08:32
  • I did look at it, yes, and tried to use it. Remember I am new to VBA and I didn't understand how to use it in my own code since we differ a little. But I did find the answer. Thank you for your help - can you set one of your replies as an answer so I can credit you? – Tensigh Sep 15 '17 at 09:04
  • Sorry, if your question was a bit more complex, I could have posted an answer. You just needed the `.Execute` method example, and it is already posted. No need to duplicate. – Wiktor Stribiżew Sep 15 '17 at 09:48

0 Answers0