CODEWITHSUNDEEP
excelvbasplit
user14505996
user14505996

Reputation:

separates the string and display just first value

Developing a function which separates the string and i want to display the first value after = that is 16 instead of entire separated string.

I hope to get some help thanks.

Sub Split()

    Dim arr() As String
    
    arr = Split("KeyNo = ,16,17,18", ",")

    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

and this one also

Sub Split2()

    Dim arr() As String
    
    arr = Split("KeyNo = 16,17,18", ",")

    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Upvotes: 2

Views: 141

Answers (3)

Brian M Stafford
Brian M Stafford

Reputation: 8868

One way to handle this is with Regular Expressions:

Option Explicit

Private Sub Test()
   MsgBox ExtractFirstNumber("KeyNo = ,16,17,18")
   MsgBox ExtractFirstNumber("KeyNo = 16,17,18")
End Sub

Private Function ExtractFirstNumber(ByVal Value As String) As String
   Dim regex As regExp
   Dim matches As MatchCollection

   Set regex = New regExp
   regex.Pattern = "\d+"
   regex.Global = True

   If regex.Test(Value) Then
      Set matches = regex.Execute(Value)
      ExtractFirstNumber = matches(0)
   End If
End Function

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

It seems that you want the 16 whether there is a comma following the "=" or not. If the comma is absent, just put it in and then use Split():

Sub splittt()
    s = "KeyNo = ,16,17,18"
    If InStr(s, "= ,") = 0 Then
        s = Replace(s, "= ", "= ,")
    End If
    
    arr = Split(s, ",")
    MsgBox arr(1)
End Sub

enter image description here

Upvotes: 0

Vityata
Vityata

Reputation: 43595

The idea of the SplitAndDisplay() code below is the following:

  • split by separator and write the result to myArr;
  • loop from lbound(myArr) to ubound(myArr) - 1, to make sure that there is always a next item;
  • if the splitAfter() is found, then display the next item;
  • if not, return -1;
Sub Split16()

    Dim textInput As String
    textInput = "KeyNo = ,16,17,18"
    
    Dim result As String
    result = SplitAndDisplay(textInput, ",", "=")
    Debug.Print result
        
End Sub

Public Function SplitAndDisplay(textInput As String, mySeparator As String, splitAfter As String)
    
    Dim myArr As Variant
    myArr = Split(textInput, mySeparator)
    
    Dim i As Long
    
    For i = LBound(myArr) To UBound(myArr) - 1
        If InStr(myArr(i), "=") Then
            SplitAndDisplay = myArr(i + 1)
            Exit Function
        End If
    Next i
    
    SplitAndDisplay = -1
    
End Function

Upvotes: 0

Related Questions