VBA逗号分隔单元格，只需要最后一个条目

Question

所以我有一个数据集，城市和地址在同一个单元格中，我想删除城市，这通常是最后一个逗号分隔的值，并把它放在旁边的单元格中示例

Answer 1

我看不到你的图像，但经过你的解释，你正在寻找以下函数:

Function Remove_last_part(StrCommaSeparated As String) As String
    Dim arr() As String
    Dim i As Integer
    arr = Split(StrCommaSeparated, ",") ' make an array out of the comma separated string
    ReDim Preserve arr(UBound(arr) - 1) ' Remove the last array element, by redimensioning the array to it's total elements minus 1
    Remove_last_part = Join(arr, ",")  ' make a comma separated string out of the redimensionned array
End Function

使用示例:

Public Sub TestIt()
    Dim strTest As String
    strTest = "anything,anywhen,anyhow,New York"
    Debug.Print "BEFORE -->" & strTest
    Debug.Print "AFTER  -->" & Remove_last_part(strTest)
End Sub

将输出:

BEFORE -->anything,anywhen,anyhow,New York
AFTER  -->anything,anywhen,anyhow