Good day;
I am trying to loop through a column and remove all characters to the right of the Final comma (including the comma itself) in each cell of that column using VBA.
For example, if cell contains: test, test2, test3
I would like to remove the ", test3" so the string now shows as "test, test2"
Problem w/ my code below is the len2 variable seems to be finding the length of string to the left of first comma, instead of length of string to Right of Final comma. Can someone help modify this, or else suggest different way?
Thank you.
I am trying to loop through a column and remove all characters to the right of the Final comma (including the comma itself) in each cell of that column using VBA.
For example, if cell contains: test, test2, test3
I would like to remove the ", test3" so the string now shows as "test, test2"
Problem w/ my code below is the len2 variable seems to be finding the length of string to the left of first comma, instead of length of string to Right of Final comma. Can someone help modify this, or else suggest different way?
Thank you.
VBA Code:
For i = lastRow to 2 step -1
len1 = Len(.Cells(i,"A").Value) ' length whole string
len2 = Len(Right(.Cells(i,"A").Value, Instr(.Cells(i,"A").Value, ",")+1))
.Cells(i,"A").Value = Left(.Cells(i,"A").Value, len1-len2))
Next i