[RESOLVED] string and substring

[luca90]

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

[passel]

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

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.

[Ordinary Guy]

How about

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

or

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

[passel]

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.

[Schmidt]

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:

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:

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

[dz32]

Smallish helper function makes it easy

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

[Zvoni]

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

[SamOscarBrown]

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().

[ChrisE]

Regex would be an option
the Pattern could look like this

 With oRegex
    .Pattern = "ab(.+?)cd" 'get Text between " ab ... cd"
    .Global = True
    .MultiLine = True
.....more code
   End With

[Zvoni]

Regex would be an option
the Pattern could look like this

 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

[Zvoni]

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)

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

[ChrisE]

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

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

2345 
12 
66

[gibra]

Another solution:

Debug.Print Trim$(Split("ab 13256 cd", " ")(1))

in Immediate Windows return: 13256

[wqweto]

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>

[Zvoni]

Another solution:

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!

[ChrisE]

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

[wqweto]

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>

[gibra]

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

[Zvoni]

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*

[ChrisE]

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

[luca90]

Another solution:

Debug.Print Trim$(Split("ab 13256 cd", " ")(1))

in Immediate Windows return: 13256

poche righe ma efficiente!
grazie

[Arnoutdv]

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"

[gibra]

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.