get sub string in string

User avatar
sal21
PlatinumLounger
Posts: 4437
Joined: 26 Apr 2010, 17:36

get sub string in string

Post by 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...

User avatar
SpeakEasy
5StarLounger
Posts: 600
Joined: 27 Jun 2021, 10:46

Re: get sub string in string

Post by 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
Last edited by SpeakEasy on 06 Jul 2023, 19:44, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get sub string in string

Post by 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
Best wishes,
Hans

User avatar
DocAElstein
5StarLounger
Posts: 651
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: get sub string in string

Post by 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
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
5StarLounger
Posts: 600
Joined: 27 Jun 2021, 10:46

Re: get sub string in string

Post by SpeakEasy »

See? :smile: