How do i get part of string after a special character?

user_5719255 0
Viewed 424

I have a column where i pickup increasing numbering values, and their format is xx_yy so the first is 1_0, second 1_1 and so forth, no we are at 23_31

I want to get the right side of the string, and i am already getting the left side correctly. using 

newActionId = Left(lastActionID, (Application.WorksheetFunction.Find("_", lastActionID, 1) - 1))

i wish to do the following, human writing below

nextSubid = entire stringvalue AFTER special character "_"

I tried just switching left to right, didnt go so well, do you have a suggestion?

vba excel
4 Answers

You can use Split function to get the relevant text.

Syntax: Split(expression, [ delimiter, [ limit, [ compare ]]])

Option Explicit

Sub Sample()
    Dim id As String
    Dim beforeSplChr As String
    Dim afterSplChr As String

    id = "23_31"

    beforeSplChr = Split(id, "_")(0)
    afterSplChr = Split(id, "_")(1)

    Debug.Print beforeSplChr
    Debug.Print afterSplChr
End Sub

Another way

Debug.Print Left(id, (InStrRev(id, "_", -1) - 1)) '<~~ Left Part
Debug.Print Right(id, (InStrRev(id, "_", -1) - 1)) '<~~ Right Part
user_11405790

Even though Siddharth Rout has given what can probably be considered a better answer here, I felt that this was worth adding:

To get the second part of the string using your original method, you would want to use the Mid function in place of Left, rather than trying to use Right.

Mid(string, start, [ length ])
Returns length characters from string, starting at the start position
If length is omitted, then will return characters from the start position until the end of the string

newActionId = Mid(lastActionID, Application.WorksheetFunction.Find("_", lastActionID, 1) + 1)
user_92909860

Just for fun (Split is the way to go here), an alternative way using regular expressions:

Sub Test()

Dim str As String: str = "23_31"

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+"
    Debug.Print .Execute(str)(0) 'Left Part
    Debug.Print .Execute(str)(1) 'Right Part
End With

End Sub

Btw, as per my comment, your first value could also be achieved through:

Debug.Print Val(str)
user_97581940

Split function of string is very usefull for this type of query. Like:

String s = "23_34";
String left = s.split("_")[0];
String right = s.split("_")[1];

Or you can also use combination of indexOf and substring method together.
String left = s.substring(0,s.indexOf('_')+1)
String right = s.substring(s.indexOf('_'));
user_83711670
Related