VBA Equivalent of a Formula

Or  you could use the Split function:

Split([E2],"_")(0)

Select allOpen in new window

Thanks Byundt,

That worked for me. Nice to hear from you after a loooong time....

Regards
Kanwal

In VBA when you have to search from the right, there is a VBA function called InStrRev. So you can use InStrRev also.

Right([E2], InStrRev([E2], "_") - 1)

Select allOpen in new window

VBA function called InStrRev. So you can use InStrRev also.

Both are InStrRev, What is the difference ?

Neeraj brings up a good point: does your worksheet formula give you the answer you are looking for?

I tested the various VBA methods with the test string "donkey_ote" and they all returned "ey_ote"

If you want the part that comes after the underscore, you might use:

Split([E2],"_")(1)        'If there is only one underscore
Mid([E2],InStr(1,[E2],"_")+1)

Select allOpen in new window

If you want to include the underscore, then you might use:

"_" & Split([E2],"_")(1)        'If there is only one underscore
Mid([E2],InStr(1,[E2],"_"))

Select allOpen in new window

I used the following. My original Excel formula was wrong and so was its VBA Equivalent.

Target.Value = Right(Target.Value, Len(Target.Value) - InStrRev(Target.Value, "_"))

InStr looks from the beginning of the string, and the first parameter is the starting position. InStr(1,"abc_def_ghi","_") returns 4 for the position of the first underscore character in the test string.

InStrRev looks from the end and does not use a starting position parameter. InStrRev("abc_def_ghi","_") returns 8 for the position of the last underscore character in the test string.

If there is only one underscore, InStr and InStrRev return the same value.

Honestly, I could not understand the Split method.
Not sure whether It will work for me or not !

If there is only one underscore, InStr and InStrRev return the same value.

Got it....

Split([E2],"_") breaks the text up into pieces, splitting the text each time it finds an underscore. It returns an array of values, so I followed the Split function with an array subscript in parentheses.

Split([E2],"_")(0) returns the first element (the part before the first underscore); it returns the entire string if there is no underscore. Split([E2],"_")(1) returns the second element; it returns all the text to the right of the underscore if there is only one such character in cell E2.

NICE EXPLANATION !

Tried it and it worked like a Charm.

Thanks Sir !!!!!!!