get sub string in string

[sal21]

...
SICILIA-19/AGRIGENTO-84
PIEMONTE-1/ALESSANDRIA-6
MARCHE-11/ANCONA-42
TOSCANA-9/AREZZO-51
MARCHE-11/ASCOLI-PICENO-44
PIEMONTE-1/ASTI-5
CAMPANIA-15/AVELLINO-64
...

For example:

Dim REGIONE as string,CODREG as string,PROVINCIA as string,CODPR as string

REGIONE=SICILIA
CODREG=19
PROVINCIA=AGRIGENTO
CODPR=84
....

ECC...

[SpeakEasy]

There are several methods. Here's one (but I am sure others will be along with alternative suggestions):

Code: Select all

' requires reference to Microsoft VBScriopt Regular Expressions 5.5
Public Sub example()
    Dim MatchSet As MatchCollection
    Dim Item As Variant
    Dim strSrc As String
    
    strSrc = "SICILIA-19/AGRIGENTO-84"
    
    With New RegExp
        .Pattern = "([A-Z]|[0-9])+"
        .Global = True
        Set MatchSet = .Execute(strSrc)
    End With
    
    For Each Item In MatchSet
        Debug.Print Item.Value 'being explicit, not relying on default
    Next
End Sub

[HansV]

Assuming that the text value "SICILIA-19/AGRIGENTO-84" is stored in a variable MYSTRING:

Code: Select all

    Dim REGIONE As String, CODREG As String, PROVINCIA As String, CODPR As String
    Dim ARR1() As String
    Dim ARR2() As String
    ARR1 = Split(MYSTRING, "/")
    ARR2 = Split(ARR1(0), "-")
    REGIONE = ARR2(0)
    CODREG = ARR2(1)
    ARR2 = Split(ARR1(1), "-")
    PROVINCIA = ARR2(0)
    CODPR = ARR2(1)
    Debug.Print REGIONE, CODREG, PROVINCIA, CODPR

[DocAElstein]

Code: Select all

Sub It()
Dim Its As Variant
    For Each Its In Split(Replace("SICILIA-19/AGRIGENTO-84", "/", "-"), "-")
     Debug.Print Its
    Next Its
End Sub

[SpeakEasy]

See?