excel - UDF 从通用字符串中提取特定数据

Question

我正在尝试创建一个类似 =Extractinfo("A2","Name") 的函数，它可以从原始数据中提取姓名、电话和电子邮件 ID，一个用于所有 3 次提取的函数，我已经有一个提取电子邮件 ID 的函数

 Raw Data

"Name":"Ram","Phone":"9898989898","Email":"abcd@gmail.com"
"Name":"Raju","Phone":"2323232323","Email":"xyz123@gmail.com"
"Name":"Rameshsing","Phone":"555999999","Email":"rameshsing@gmail.com"

(Function to extract)          (Expected Result)
=Extractinfo("A2","Name")      Ram
=Extractinfo("A2","Name")      Raju
=Extractinfo("A4","Name")      Rameshsing


Function ExtractEmailFun(extractStr As String) As String

 Dim CharList As String
 On Error Resume Next
CheckStr = "[A-Za-z0-9._-]"
OutStr = ""
Index = 1
Do While True
Index1 = VBA.InStr(Index, extractStr, "@")
getStr = ""
If Index1 > 0 Then
    For p = Index1 - 1 To 1 Step -1
        If Mid(extractStr, p, 1) Like CheckStr Then
            getStr = Mid(extractStr, p, 1) & getStr
        Else
            Exit For
        End If
    Next
    getStr = getStr & "@"
    For p = Index1 + 1 To Len(extractStr)
        If Mid(extractStr, p, 1) Like CheckStr Then
            getStr = getStr & Mid(extractStr, p, 1)
        Else
            Exit For
        End If
    Next
    Index = Index1 + 1
    If OutStr = "" Then
        OutStr = getStr
    Else
        OutStr = OutStr & Chr(10) & getStr
    End If
Else
      Exit Do
End If
Loop
ExtractEmailFun = OutStr
End Function

Answer 1

您的 RAW 数据似乎与 JSON 格式匹配。可以做的一件事是使用一个模块(如 this one 对我来说效果很好)来解析它(即在其中放置一些顺序，这样您就可以轻松获得所需的结果)。

关注 simple instructions将此模块添加到您的 VBA 项目中(记得添加 Microsoft.Scripting Reference!)。然后你的函数看起来像这样:

Public Function Extractinfo(byval CompleteString as String, byval FieldName as String) as String

    Dim JSON as Object
    Set JSON = JsonConverter.ParseJson(CompleteString)

    ExtractInfo = JSON(FieldName)

End Function

此代码示例过于简化(没有任何错误处理)，但这应该可以帮助您入门。