这实际上一点也不简单。但不妨试试：

A3中的公式：

=DROP(DROP(REDUCE(0,MID(A1,SEQUENCE(LEN(A1)),1),LAMBDA(a,b,TOCOL(LET(x,TAKE(a,1),IF(b="""",VSTACK(NOT(x),DROP(a,1)),IF(x+ISNUMBER(--b),VSTACK(DROP(a,-1),TAKE(a,-1)&b),VSTACK(a,"")))),3))),1),-1)

我不认为这会勾住你的边缘案件。

我对自己的问题有了一个突破口。这似乎对所有情况都有效。有没有人能让这个更有效率？

Function TEXTTOARRAY(inarr As String)
    Dim nDbleQuote As Long
    Dim charLng As String
    Dim BrkElum() As Long
    Dim lenArr As Long
    Dim nCol As Long, nRow As Long, nElum As Long
    Dim iLng As Long, iRows As Long, iCols As Long, iElum As Long
    Dim RowSep As String, ColSep As String
    RowSep = Application.International(xlRowSeparator)
    ColSep = Application.International(xlColumnSeparator)
    
    'Remove curly brackets
    Dim Arr As String: Arr = Mid$(inarr, 2, Len(inarr) - 2)
    
    ReDim BrkElum(1 To 1): BrkElum(1) = 0
    
    nElum = 1
    nRow = 1
    nCol = 1
    
    lenArr = Len(Arr)
    
    'Iterate through string and find break points
    For iLng = 1 To lenArr
         charLng = Mid$(Arr, iLng, 1)
        If charLng = Chr(34) Then nDbleQuote = nDbleQuote + 1
        If WorksheetFunction.IsEven(nDbleQuote) Then
            If charLng = ColSep Then
                If nRow = 1 Then nCol = nCol + 1
                nElum = nElum + 1
                ReDim Preserve BrkElum(1 To nElum)
                BrkElum(nElum) = iLng
            ElseIf charLng = RowSep Then
                nRow = nRow + 1
                nElum = nElum + 1
                ReDim Preserve BrkElum(1 To nElum)
                BrkElum(nElum) = iLng
            End If
        End If
    Next iLng
    
    ReDim Preserve BrkElum(1 To nElum + 1)
    BrkElum(nElum + 1) = lenArr + 1
    
    'Create array
    Dim ArrOut() As Variant
    ReDim ArrOut(1 To nRow, 1 To nCol)
    For iRows = 1 To nRow
        For iCols = 1 To nCol
            iElum = (iRows - 1) * nCol + iCols
            ArrOut(iRows, iCols) = Mid$(Arr, BrkElum(iElum) + 1, BrkElum(iElum + 1) - BrkElum(iElum) - 1)
            If Left$(ArrOut(iRows, iCols), 1) = Chr(34) Then 'Remove outside quotes and replace internal double double quotes with single double quotes
                ArrOut(iRows, iCols) = Replace(Mid$(ArrOut(iRows, iCols), 2, Len(ArrOut(iRows, iCols)) - 2), Chr(34) & Chr(34), Chr(34))
            ElseIf IsNumeric(ArrOut(iRows, iCols)) Then 'Check if numeric and if so change from text to number
                ArrOut(iRows, iCols) = CDbl(ArrOut(iRows, iCols))
            End If
        Next iCols
    Next iRows
    
    TEXTTOARRAY = ArrOut
    
End Function

您可以在B4:D6中的原始范围下方链接的图像中看到。
你可以在B8ARRAYTOTEXT(B4:D6,1)中看到。
您可以在B10:B12TEXTTOARRAY(B8)中看到（所需的功能）。
在B14:D16中可以看到B4:D6=B10:D12中的所有细胞。
How it has worked out

根据键入公式={1,"Semicolon ; in me";"Comma , in me",4}会产生所需结果的观察结果

这可以作为UDF使用Evaluate来完成，并且对输入的数据类型敏感

Function TextToArray(r As Variant) As Variant
    If TypeOf r Is Range Then
        TextToArray = Application.Evaluate(r.Value2)
    Else
        TextToArray = Application.Evaluate(r)
    End If
End Function

对于不完整的输入（例如{"a",}），你必须对输入进行预处理以添加缺失的元素（例如一个空字符串""），但是如果你这样做了，你也可以只处理整个字符串。
请注意，此答案基于您的输入字符串是否为有效公式。{"a",}不是有效公式。