VBA comma separated cells, needt last entry only

I can’t see your image but after your explanation, you are looking for the following function:

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

Example on how to use it :

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

Will output :

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

Browse More Popular Posts

Leave a Comment