Converting Single Cell Array Results into Single Concatenated String

Question

Hello I am currently working on a project to bring in Regular Expressions into Excel using the VBSCRIPT_RegExp_1. I am using the vba code below.

Function RegExGet(aString As String, myExpression As String) As Variant             
    Dim RegEx As New VBScript_RegExp_10.RegExp
    Dim newArray() As String                                                            

    RegEx.Pattern = myExpression                                                        
    RegEx.IgnoreCase = True                                                             
    RegEx.Global = True                                                                 
    Set Matches = RegEx.Execute(aString)                                                

    x = Matches.Count                                                                   
    ReDim newArray(x - 1) As String                                                                                                                                      
    cnt = 0

       For Each Match In Matches                                                        
         newArray(cnt) = Match.Value                                                            
         cnt = cnt + 1                                                               
       Next                                                                            

    RegExGet = newArray()
End Function

The problem with the code is that if returns multiple results, only the first will show up. It will create a 1 by 1 array where any other results besides the first is hidden. Wh

What I am hoping for is a modification so that the array that is produced, at whatever size it may be, concatenates all the results with a ";" between them. Ie "1; 2; 3;". Any tips on how JOIN or CONCATENATE may be used would be most welcome!

Thanks and have a nice day!

SJR · Accepted Answer · 2018-01-12 16:33:55Z

1

You really just need to add the Join to the last line.

Function RegExGet(aString As String, myExpression As String) As String
    Dim RegEx As New VBScript_RegExp_10.RegExp
    Dim newArray() As String
    Dim x As Long, cnt As Long
    Dim Matches As Object, Match As Object

    RegEx.Pattern = myExpression
    RegEx.IgnoreCase = True
    RegEx.Global = True
    Set Matches = RegEx.Execute(aString)

    x = Matches.Count
    ReDim newArray(x - 1) As String
    cnt = 0

       For Each Match In Matches
         newArray(cnt) = Match.Value
         cnt = cnt + 1
       Next

    RegExGet = Join(newArray(), ";")
End Function

'example
Sub y()

MsgBox RegExGet("123456789", "\d")

End Sub

answered Jan 12, 2018 at 16:33

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Converting Single Cell Array Results into Single Concatenated String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related