dcsimg
Results 1 to 23 of 23

Thread: [RESOLVED] string and substring

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,918

    Resolved [RESOLVED] string and substring

    i have myvar="ab 13256 cd"

    i need to get the trimmed value between ab and cd

    in my case:

    newvar="13256"

    how to?

    note:
    the position of ab and cd are variable

  2. #2
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    5,532

    Re: string and substring

    Are "ab" and "cd" literal, or at least two known two character strings?
    Will there be any space characters within the string between "ab" and "cd"?

    If I take the question literally, then without actually trying it in code, my shot would be
    Code:
    Dim s As Long, e As Long
    s = Instr(myvar, "ab")
    If s > 0 then 'we found it
      s = s + 2  'skip over the tag
      e = Instr(s, myvar, "cd")
      If e > s Then
        e = e -1
        newvar = Trim$(Mid$(myVar, s, e-s))
      End If
    End If
    May not need to subtract 1 from e, but probably doesn't hurt unless there was no spaces after the embedded string.

    Since I just typed the code above in the response, without trying it in the IDE, there could be minor syntax issues, or perhaps logic issues, but it should be close to a working solution.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  3. #3
    Addicted Member
    Join Date
    Mar 2017
    Posts
    149

    Re: string and substring

    How about

    myvar = Trim(Replace(myvar, "ab", ""))
    myvar = Trim(Replace(myvar, "cd", ""))

    or

    newvar = Trim(Replace(myvar, "ab", ""))
    newvar = Trim(Replace(newvar, "cd", ""))

  4. #4
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    5,532

    Re: string and substring

    I guess that is valid assuming the strings are as stated.
    I guess I assumed the given string my be a substring within a larger string, but there was no reason to assume that, based on the question itself.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,405

    Re: string and substring

    If the sequence of "... [StringPrefix] SomeNumber [StringSuffix] ..." is part of a larger text-snippet,
    then a simple way would be e.g. a little function like:

    Code:
    Function GetNumberBehind(sPrefix, sContent) As String
      GetNumberBehind = Val(Split(sContent, sPrefix)(1))
    End Function
    The above would then be able, to return the Number 123 when called with this input:
    Code:
    Debug.Print GetNumberBehind("ab", "in some longer text ... ab 123 cd ...")
    But we are all guessing here... in the end, the OP probably wants to parse out stuff from behind html- or xml-node-tags,
    and for that purpose exist Helper-COM-Objects on any Win-System.

    Olaf

  6. #6
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    313

    Re: string and substring

    Smallish helper function makes it easy

    Code:
    Private Sub Form_Load()
        Dim myVar As String, retVal As String
          
        myVar = "this is my long text ab 13256 cd with other stuff in it"
          
        If Extract(myVar, "ab ", " cd", retVal)  > 0 Then
            MsgBox """" & retVal & """"
        Else
             MsgBox "Markers Not found"
        End If
          
    End Sub
    
    'Note: function return value is length of outVar string
    'if marker1 = "" start beginning, if marker2="" goto end of string, 
    Function Extract(value, marker1, marker2, ByRef outVar, _
                    Optional includeMarkers As Boolean = False, _
                    Optional start As Long = 1, _
                    Optional ByRef lastPos As Long, _
                    Optional method As VbCompareMethod = vbBinaryCompare _
    ) As Long
    
        Dim a As Long, b As Long
        
        lastPos = 0
        outVar = Empty
        
        If Len(marker1) = 0 Then
            a = 1
        Else
            a = InStr(start, value, marker1, method)
            If a < 1 Then Exit Function
        End If
        
        a = a + Len(marker1)
        If Len(marker2) = 0 Then
            outVar = Mid(value, a)
        Else
            b = InStr(a, value, marker2, method)
            If b < 1 Then Exit Function
            
            lastPos = b + Len(marker2)
            outVar = Mid(value, a, b - a)
        End If
        
        If includeMarkers Then outVar = marker1 & outVar & marker2
        Extract = Len(outVar)
        
    End Function

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,973

    Re: string and substring

    Hmmm, if it's always "SomeText - SomeNumbers - Sometext" i'd rather go the route with the strCSpn/Strspn-API-Combination, then it doesn't matter what Text (which/how many characters are before or after the number you're looking for).
    The Result of StrCSpn+1 would be the Start of the Substring, strspn the Length of the SubString, throw it at Mid$ and you're done
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,188

    Re: string and substring

    y'all posted some great suggestions. MY suggestion would be, FIRST, ask OP what he/she has tried---post his/her attempt. How else is Luca90 ever going to learn? OP has a habit of 'asking for code', instead of really, 'this doesn't work, what did I do wrong?"

    Or, to help OP initially (if he/she has NO idea where to start), I might have suggested (in this case) to go to MSDN Help and research Instr(), and possibly, Trim().

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,096

    Re: string and substring

    Regex would be an option
    the Pattern could look like this

    Code:
     With oRegex
        .Pattern = "ab(.+?)cd" 'get Text between " ab ... cd"
        .Global = True
        .MultiLine = True
    .....more code
       End With
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,973

    Re: string and substring

    Quote Originally Posted by ChrisE View Post
    Regex would be an option
    the Pattern could look like this

    Code:
     With oRegex
        .Pattern = "ab(.+?)cd" 'get Text between " ab ... cd"
        .Global = True
        .MultiLine = True
    .....more code
       End With
    Chris, i was thinking about that, too, but, frankly, Regex just looks like Chinese in backwards to me
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,973

    Re: string and substring

    Code:
    Private Declare Function StrSpn Lib "SHLWAPI" Alias "StrSpnW" (ByVal lpSource As Long, ByVal lpCharSet As Long) As Long
    Private Declare Function StrCSpn Lib "SHLWAPI" Alias "StrCSpnW" (ByVal lpSource As Long, ByVal lpCharSet As Long) As Long
    Private Const DIGITS As String = "0123456789"
    
    Sub Main()
    Dim MyString As String
    Dim NewVar As String
        MyString = "ab 12356 cd"
        NewVar = Left$(Mid$(MyString, StrCSpn(StrPtr(MyString), StrPtr(DIGITS)) + 1), StrSpn(StrPtr(Mid$(MyString, StrCSpn(StrPtr(MyString), StrPtr(DIGITS)) + 1)), StrPtr(DIGITS)))
        Debug.Print NewVar  'Prints "12356"
    End Sub
    EDIT: Tiny bit optimized (not calling StrCSpn twice)
    Code:
    Private Declare Function StrSpn Lib "SHLWAPI" Alias "StrSpnW" (ByVal lpSource As Long, ByVal lpCharSet As Long) As Long
    Private Declare Function StrCSpn Lib "SHLWAPI" Alias "StrCSpnW" (ByVal lpSource As Long, ByVal lpCharSet As Long) As Long
    Private Const DIGITS As String = "0123456789"
    
    Sub Main()
    Dim MyString As String
    Dim NewVar As String
    Dim MyCSpn As String
    Dim MySpn As Long
        MyString = "ab 12356 cd"
        MyCSpn = Mid$(MyString, StrCSpn(StrPtr(MyString), StrPtr(DIGITS)) + 1)
        MySpn = StrSpn(StrPtr(MyCSpn), StrPtr(DIGITS))
        NewVar = Left$(MyCSpn, MySpn)
        Debug.Print NewVar  'Prints 12356
    End Sub
    Last edited by Zvoni; Oct 9th, 2019 at 01:47 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,096

    Re: string and substring

    Quote Originally Posted by Zvoni View Post
    Chris, i was thinking about that, too, but, frankly, Regex just looks like Chinese in backwards to me
    wll Regex isn't that difficult
    here translated from Chinese to...

    Code:
    Option Explicit
    Private pRegEx As Object
    
    Public Property Get oRegex() As Object
       If (pRegEx Is Nothing) Then
          Set pRegEx = CreateObject("Vbscript.Regexp")
       End If
       Set oRegex = pRegEx
    End Property
    
    
    
    Private Sub Command1_Click()
       Dim cMatches As Object
       Dim m As Object
    
       Dim tmpText As String
       tmpText = "search test ab 2345 cd and what if more ab 12 cd is in ab 66 cd text"
       
       With oRegex
       .Pattern = "ab(.+?)cd" 'get all Text between "..."
        .Global = True
        .MultiLine = True
          Set cMatches = .Execute(tmpText)
             For Each m In cMatches
               Debug.Print m.SubMatches(0)
           Next
       End With
       Set m = Nothing
       Set cMatches = Nothing
    End Sub
    Output
    Code:
    2345 
    12 
    66
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,667

    Re: string and substring

    Another solution:

    Code:
    Debug.Print Trim$(Split("ab 13256 cd", " ")(1))
    in Immediate Windows return: 13256

  14. #14
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,582

    Re: string and substring

    Quote Originally Posted by ChrisE View Post
    wll Regex isn't that difficult
    here translated from Chinese to...
    Why the whole "ab(.+?)cd" pattern? Just use "\d+(\.\d*)?" for a number bound by *any* prefix and/or *any* suffix :-))

    cheers,
    </wqw>

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,973

    Re: string and substring

    Quote Originally Posted by gibra View Post
    Another solution:

    Code:
    Debug.Print Trim$(Split("ab 13256 cd", " ")(1))
    in Immediate Windows return: 13256
    Nice one!
    But keep in mind, this is only going to work if you actually have something between text and number you can use as a delimiter.
    My code works without space/blanks between text and number.
    But a valid solution to the problem as posted!
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,096

    Re: string and substring

    Quote Originally Posted by wqweto View Post
    Why the whole "ab(.+?)cd" pattern? Just use "\d+(\.\d*)?" for a number bound by *any* prefix and/or *any* suffix :-))

    cheers,
    </wqw>
    your right if he is always looking for numbers only
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,582

    Re: string and substring

    Quote Originally Posted by ChrisE View Post
    your right if he is always looking for numbers only
    I was not keen on the idea OP was searching for "ab" prefix nor "cd" suffix anytime at all.

    The actual prefixes/suffixes must be either confidential or explicit language in violation of forums terms :-))

    cheers,
    </wqw>

  18. #18
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,667

    Re: string and substring

    Quote Originally Posted by Zvoni View Post
    But keep in mind,
    I know.

    As I said, it's just another solution.
    Knowing that the user, as he often does, does not sufficiently explain his need, I followed the example string.
    But I suspect there's something else underneath ...


  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,973

    Re: string and substring

    Quote Originally Posted by gibra View Post
    I know.

    As I said, it's just another solution.
    Knowing that the user, as he often does, does not sufficiently explain his need, I followed the example string.
    But I suspect there's something else underneath ...

    Well, Luca is italian, but so are you.
    takes one italian to know another........
    *runaway*
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,096

    Re: string and substring

    Quote Originally Posted by wqweto View Post
    I was not keen on the idea OP was searching for "ab" prefix nor "cd" suffix anytime at all.

    The actual prefixes/suffixes must be either confidential or explicit language in violation of forums terms :-))

    cheers,
    </wqw>
    LOL, probably a Safe combination
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  21. #21

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    1,918

    Re: string and substring

    Quote Originally Posted by gibra View Post
    Another solution:

    Code:
    Debug.Print Trim$(Split("ab 13256 cd", " ")(1))
    in Immediate Windows return: 13256
    poche righe ma efficiente!
    grazie

  22. #22
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,609

    Re: string and substring

    If this is the solution then the question should have been "how to get the 2nd value from a string where the values are separated by single spaces"

  23. #23
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,667

    Re: string and substring

    Quote Originally Posted by luca90 View Post
    poche righe ma efficiente!
    grazie
    So you resolved?
    If yes set the thread as RESOLVED.

    P.S.
    Quindi, hai risolto?
    Se sė imposta il flag RISOLTO alla discussione.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width