VBA: How to find the values after a "@" symbol in a string

Question

I am trying to set the letters after a @ symbol to a variable.

For example, x = @BAL

I want to set y = BAL

Or x = @NE

I want y = NE

I am using VBA.

Answer 1

Split() in my opinion is the easiest way to do it:

Dim myStr As String
myStr = "@BAL"
If InStr(, myStr, "@") > 0 Then '<-- Check for your string to not throw error
    MsgBox Split(myStr, "@")(1)
End If

As wisely pointed out by Scott Craner, you should check to ensure the string contains the value, which he checks in this comment by doing: y = Split(x,"@")(ubound(Split(x,"@")). Another way you can do it is using InStr(): If InStr(, x, "@") > 0 Then...

The (1) will take everything after the first instance of the character you are looking for. If you were to have used (0), then this would have taken everything before the @.

---

Similar but different example:

Dim myStr As String
myStr = "@BAL@TEST"

MsgBox Split(myStr, "@")(2)

The message box would have returned TEST because you used (2), and this was the second instance of your @ character.

---

Then you can even split them into an array:

Dim myStr As String, splitArr() As String
myStr = "@BAL@TEST"

splitArr = Split(myStr, "@")   '< -- don't append the collection number this time

MsgBox SplitArr(1)  '< -- This would return "BAL"
MsgBox SplitArr(2)  '< -- This would return "TEST"

---

If you are looking for additional reading, here is more from the MSDN:

Split Function

Description Returns a zero-based, one-dimensional array containing a specified number of substrings. SyntaxSplit( expression [ ,delimiter [ ,limit [ ,compare ]]] ) The Split function syntax has thesenamed arguments:

expression

Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.

delimiter

Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.

limit

Optional. Number of substrings to be returned; -1 indicates that all substrings are returned.

compare

Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.