If the last character in a cell is "A", delete that character from the cell

Question

Worksheets("1").Activate

  Dim LR As Long
  LR = Cells(Rows.Count, "D").End(xlUp).Row
  Range("D2:D" & LR) = Evaluate(replace("IF(D2:D#=""A"",""A"",LEFT(D2:D#,FIND(A|A,SUBSTITUTE(D2:D#,RIGHT(TRIM(D2:D#)),A|A,LEN(D2:D#)-LEN(SUBSTITUTE(D2:D#,RIGHT(TRIM(D2:D#)),""A""))))))", "#", LR))
End Sub

I've been trying to rework this code to loop through column D until end, and delete any "A" characters from the cells, if that "A" is found at the end.

So far I'm just returning a #VALUE.

Thanks for any input.

Answer 1

I expect that you could write a formula to do this (with "evaluate") but it's pretty straightforward with pure VBA:

Sub chomp_A()
  Dim row As Long
  Dim LR As Long
  LR = Cells(Rows.Count, "D").End(xlUp).row
  For row = 2 To LR
    Do While Right(Cells(row, "D").Value, 1) = "A"
      Cells(row, "D").Value = Left(Cells(row, "D").Value, Len(Cells(row, "D").Value) - 1)
    Loop
  Next
End Sub

This will remove all trailing "A" characters from text in column D. So "SPARTA" would become "SPART" and "RUFIYAA" woudl become "RUFIY"