Find the letters in a string

[julie_nickb]

Hello,
In VBA, I would like to split a string variable in two, at the point where the first time "_" is used.

For example, if the string is "ABC_12345_asd", I would like to split it into "ABC" and "_12345_asd".
I know how to do this is Excel using the "FIND" and "LEFT" functions, but having problems in VBA.
Thanks for your help.










Tt

 

[Dan_W]

You will need to use the Split function. In your example above, you would get the desire result with the following:

TestString = "ABC_12345_asd"
Results = Split(TestString, "_", 2)

The resulting two parts are now stored in the Results variable as Results(0) and Results(1). To help you understand how it works, I prepared a short subroutine below so you could see/test the process. When using the Split function, it removes the delimiting character ( the undersscore ) so, as below, you need to just put it back in.

Sub SplitString()

Dim TestString As String, Results As Variant

TestString = "ABC_12345_asd"
Results = Split(TestString, "_", 2)
Debug.Print Results(0)          '  Output:    ABC
Debug.Print Results(1)          '  Output:    12345_asd

Results(1) = "_" & Results(1)   '  Need to put the underscore back before the second part

Debug.Print Results(1)          '  Output:    _1234_asd

End Sub

Hope that helps.

 

[JoeMo]

Here's a UDF (VBA - User Defined Function) you can use like a worksheet function (see example below) or in a VBA subroutine (e.g. the sub below the udf).

Book1
	A	B	C
1	ABC_12345_asd	ABC	_12345_asd
2	ABC12345asd	ABC12345asd
Sheet1

Cell Formulas
Range	Formula
B1:C2	B1	=StrangeSplit(A1,"_")
Press CTRL+SHIFT+ENTER to enter array formulas.

Function StrangeSplit(S As String, Ch As String) As Variant()
Dim Spl(1)
If InStr(S, Ch) = 0 Then
    Spl(0) = S
    Spl(1) = ""
    StrangeSplit = Spl
    Exit Function
End If
Spl(0) = Left(S, InStr(S, Ch) - 1)
Spl(1) = Mid(S, InStr(S, Ch), Len(S))
StrangeSplit = Spl
End Function
Sub Testit()
Dim X As Variant
X = StrangeSplit(Range("A1").Value, "_")
MsgBox X(0) & vbNewLine & X(1)
End Sub

 

[kvsrinivasamurthy]

Try this code

Sub SplitText()
Dim T As Integer
Dim Str As String, Rsl1 As String, Rsl2 As String
Str = "ABC_564_tryre"

For T = 1 To Len(Str)
    If Mid(Str, T, 1) = "_" Then
    Rsl1 = Left(Str, T - 1)
    Rsl2 = Right(Str, Len(Str) - T + 1)
    Exit For
    End If
Next T

End Sub

 

[julie_nickb]

You will need to use the Split function. In your example above, you would get the desire result with the following:

TestString = "ABC_12345_asd"
Results = Split(TestString, "_", 2)

The resulting two parts are now stored in the Results variable as Results(0) and Results(1). To help you understand how it works, I prepared a short subroutine below so you could see/test the process. When using the Split function, it removes the delimiting character ( the undersscore ) so, as below, you need to just put it back in.

Sub SplitString()

Dim TestString As String, Results As Variant

TestString = "ABC_12345_asd"
Results = Split(TestString, "_", 2)
Debug.Print Results(0)          '  Output:    ABC
Debug.Print Results(1)          '  Output:    12345_asd

Results(1) = "_" & Results(1)   '  Need to put the underscore back before the second part

Debug.Print Results(1)          '  Output:    _1234_asd

End Sub

Hope that helps.

Thank you, it's brilliant. I will investigate what other VBA functions exist.