Extracting Text from Cell, VBA Loop

[cart0250]

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.

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

 

[Joe4]

Look at using InStrRev instead, which will find the LAST comma instread of the first one.
Then is is just a matter of using the LEFT function, going to the number returned by InStrRev minus 1.

See: MS Excel: How to use the INSTRREV Function (VBA)

 

[maabadi]

Try this:

For i = lastRow to 2 step -1
   len2 = InStrRev(.Cells(i, "A").Value, ",")
  .Cells(i, "A").Value = Left(.Cells(i, "A").Value, len2)
Next i

 

[cart0250]

Try this:

For i = lastRow to 2 step -1
   len2 = InStrRev(.Cells(i, "A").Value, ",")
  .Cells(i, "A").Value = Left(.Cells(i, "A").Value, len2)
Next i

Thank you both! This works with -1 on the len2 to remove the comma.

 

[maabadi]

You're Welcome & Thanks for feedback.