4

Ok, to start. I'm a little rusty on VBA, 3 + years since Ive need to use it.

In short, im struggling to extract text from a string. Im using regular expression to extract my department name and date from this string.

The Department will always fall between : and -.

I can't share the document due to security. But, I can explain the format and hopefully we can work from that.

Col A----Col B----Col C---Col D

Date(e)--Dept(e)--String--Duration

Where (e) means it was extracted from the string.

String Example

My code for the extraction, thus far, is below. Currently it will loop through all available rows and extract the department, but it always take the : and - with it! I can't seem to find a way to cut these out.

Any assistance?

I can probably work out the date bit eventually.

The final output from this code is ": Inbound Contacts -" Where I need, "Inbound Contacts". 

Sub stringSearch()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim matches As Variant, match As Variant
    Dim Reg_Exp As Object

    Set Reg_Exp = CreateObject("vbscript.regexp")

    Reg_Exp.Pattern = "\:\s(\w.+)\s\-"


    Set ws = Sheet2

    lastRow = ws.Range("C" & Rows.Count).End(xlUp).Row

    For x = 1 To lastRow
        Set matches = Reg_Exp.Execute(CStr(ws.Range("C" & x).Value))
        If matches.Count > 0 Then
            For Each match In matches
                ws.Range("B" & x).Value = match.Value
            Next match
        End If
    Next x
End Sub
Improve this question
0

4 Answers 4

Reset to default
5

This is how to achieve what you want without , in general it should be a bit faster and way more understandable:

Sub TestMe()

    Dim inputString As String
    inputString = "Planning Unit: Inbound Contacts = Tuesday, 27/03/2018"
    Debug.Print Split(Split(inputString, ":")(1), "=")(0)

End Sub
  • split the inputString by : and take the second part;
  • split the taken part by = and take the first part;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

You are not accessing Group 1 value.

Instead of ws.Range("B" & x).Value = match.Value use

ws.Range("B" & x).Value = match.Submatches(0)

You may also enhance the regex a bit to

Reg_Exp.Pattern = ":\s*(\w.*?)\s*-"

This way, you will "trim" the Group 1 value. See the regex demo.

Details

  • : - a : char
  • \s* - 0+ whitespace chars
  • (\w.*?) - Group 1 (.Submatches(0)): a word char followed with any 0+ chars (other than line break chars) as few as possible (NOTE that \w does not match non-ASCII letters, probably you want to match any char that is not whitespace and not a -, then use [^\s-] instead of \w)
  • \s* - 0+ whitespace chars
  • - - a hyphen.
Improve this answer

3 Comments

RegEx beginner here. Just curious what issues would :\s*(.+)\s*?- pattern will pose?
@shrivallabha.redij In that case, \s*? is redudnat, Group 1 value will always contain all the trailing whitespace chars it can find on its way to -.
Thank you! Worked perfectly. I knew it had something to do with calling the group. But I couldnt work out how in VBA!
1

Regex:

You can use this Regex: ([\s\S]+?):\s*([\s\S]+?)\s*-\s*([A-z]+)\s*,\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4})\b

And the demo

Code:

And this code:

Sub stringSearch()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim matches As Variant, match As Variant
    Dim Reg_Exp As Object

    Set Reg_Exp = CreateObject("vbscript.regexp")

    Reg_Exp.Pattern = "([\s\S]+?):\s*([\s\S]+?)\s*-\s*([A-z]+)\s*,\s*([0-9]{2}\/[0-9]{2}\/[0-9]{4})\b"


    Set ws = Sheet2

    lastRow = ws.Range("C" & Rows.Count).End(xlUp).Row

    For x = 1 To lastRow
        Set matches = Reg_Exp.Execute(CStr(ws.Range("C" & x).Value))
        If matches.Count > 0 Then
            For Each match In matches
                For i = 0 To match.SubMatches.Count - 1
                    Debug.Print match.SubMatches(i)
                Next i
            Next match
        End If
    Next x
End Sub

Result

This is the result on the immediate window:

+-------------------+
| Planning Unit     |
| Inbound Contracts |
| Tuesday           |
| 27/03/2018        |
| Planning Unit     |
| Payments & Orders |
| Tuesday           |
| 27/03/2018        |
| Planning Unit     |
| Scheduling        |
| Tuesday           |
| 27/03/2018        |
+-------------------+
Improve this answer

Comments

1

I'd use Left/Right/Mid and InStr/InStrRev instead of RegEx in this case.

For extracting the department:

Dim mainStr As String
Dim deptStr As String
mainStr = "Planning Unit: Inbound Contacts - Tuesday, 27/03/2018"
deptStr = Mid(mainStr, InStr(mainStr, ":") + 2)
deptStr = Left(deptStr, InStr(deptStr, "-") - 2)

For extracting the date:

Dim mainStr As String
Dim dateStr As String
mainStr = "Planning Unit: Inbound Contacts - Tuesday, 27/03/2018"
dateStr = Right(mainStr, Len(mainStr) - InStrRev(mainStr, " "))

To be honest, this kind of situation is common enough that you might want to write some sort of "extractText" function to get the text between delimiters. Here's the one I use.

Function extractText(str As String, leftDelim As String, rightDelim As String, _
                     Optional reverseSearch As Boolean = False) As String
'Extracts text between two delimiters in a string
'By default, searches for first instance of each delimiter in string from left to right
'To search from right to left, set reverseSearch = True
'If left delimiter = "", function returns text up to right delimiter
'If right delimiter = "", function returns text after left delimiter
'If left or right delimiter not found in string, function returns empty string

    Dim leftPos As Long
    Dim rightPos As Long
    Dim leftLen As Long
    If reverseSearch Then
        leftPos = InStrRev(str, leftDelim)
        rightPos = InStrRev(str, rightDelim)
    Else
        leftPos = InStr(str, leftDelim)
        rightPos = InStr(str, rightDelim)
    End If

    leftPos = IIf(leftDelim = "", -1, leftPos)
    rightPos = IIf(rightDelim = "", -1, rightPos)
    leftLen = Len(leftDelim)

    If leftPos > 0 Then
        If rightPos = -1 Then
            extractText = Mid(str, leftPos + leftLen)
        ElseIf rightPos > leftPos Then
            extractText = Mid(str, leftPos + leftLen, rightPos - leftPos - leftLen)
        End If
    ElseIf leftPos = -1 Then
        If rightPos > 0 Then
            extractText = Left(str, rightPos - 1)
        End If
    End If

End Function
Improve this answer

2 Comments

' The provided code snippet lacks proper syntax and use of functions for string handling and regular expressions in typical programming languages like VBA or Visual Basic.Net. ' Here are some key points and corrections: ' 1. Declare Function: ' - Declare Function IRegExp (ByRef IReg As String, ByVal Exp As Integer) As String is a declaration style commonly seen in VB6 or VBA to use external DLL functions but is not typically how you handle regex. Usually, regex is handled via objects like VBScript_RegExp_55.RegExp in VBA or System.Text.RegularExpressions.Regex in VB.Net.
' 2. Pattern and Flags Handling: ' - The variable pattern is just a simple string "pizza/coke/paste". ' - The variable flags is "delivery" but in classic regex usage, flags like g, i, or m are used, not arbitrary strings. ' - The If statements check for exact equality and then print or try to operate on strings using incomplete syntax like Left(pattern, 2512) and right(flags, 164) without assignment or proper usage. ' 3. Correct Usage for RegExp in VBA (example): ' ```vb

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.