string - VBA:如何在字符串中查找 "@"符号之后的值

Question

我正在尝试将 @ 符号后面的字母设置为变量。

例如，x = @BAL

我想设置 y = BAL

或 x = @NE

我想要y = NE

我正在使用 VBA。

Answer 1

Split() 在我看来是最简单的方法:

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

正如 Scott Craner 明智地指出的那样，您应该检查以确保字符串包含他在 this comment 中检查的值通过这样做: y = Split(x,"@")(ubound(Split(x,"@"))。另一种方法是使用 InStr(): If InStr(, x, "@") > 0 Then...

(1) 将获取您要查找的字符的第一个实例之后的所有内容。如果您要使用 (0)，那么这将占用 @ 之前的所有内容。

---

类似但不同的例子:

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

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

消息框将返回 TEST，因为您使用了 (2)，这是您的 @ 字符的第二个实例。

---

然后你甚至可以将它们拆分成一个数组:

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"

---

如果您正在寻找更多阅读内容，请参阅 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.

分隔符

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.

限制

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

比较

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