Extract char/numbers using Mid and Pos function-CodePudding

I have a string with a value of "String_test|123456"

How can I extract the numbers and characters and put them to another variable string2 = "String_test" and int = 123456 using Mid / Pos functions.

Thanks!

CodePudding user response：

You can modify this user defined function to suit your needs.

Public Function ReturnIntegers(cell As Range) As String

   Dim stringholder As String
   stringholder = cell.value
   Dim pos As Integer
   pos = InStr(1, stringholder, "|", vbTextCompare)
   ReturnIntegers = Right(stringholder, (Len(stringholder) - pos))
   
End Function

CodePudding user response：

Assuming | is the delimiter, it's easier to use Split function to do this.

Sub Test()
    Const sampleStr As String = "String_test|123456"
    
    Dim splitArr() As String
    splitArr = Split(sampleStr, "|")
    
    Dim string2 As String
    string2 = splitArr(0)
    Debug.Print string2 'String_test
    
    Dim int2 As Long
    int2 = splitArr(1)
    Debug.Print int2 '123456
End Sub