VBA to delete last character in cell....conditionally

[umpscott]

I would like to create vba code to remove the last character in a cell, but only in the case that the last character is a ~. It is ok for the ~ character to occur elsewhere, just not as the last character.

Thanks in advance.

 

[SirJB7]

Hi, umpscott!

Tried with something like this?

    With ActiveCell
        If Right(.Value, 1) = "~" Then .Value = Right(.Value, Len(.Value) - 1)
    End With

Regards!

 

[Debraj]

In formula approach..

=LEFT(A1,LEN(A1)-(RIGHT(A1)="~"))

 

[umpscott]

Hi, umpscott!

Tried with something like this?

    With ActiveCell
        If Right(.Value, 1) = "~" Then .Value = Right(.Value, Len(.Value) - 1)
    End With

Regards!

I like this thought..... but here is what I tried.

I would like to do this to multiple cells in a particular column (Field)

Range("DataTable[Pred_Description]").Select
  With Selection
  If Right(.Value, 1) = "~" Then .Value = Right(.Value, Len(.Value) - 1)
  End With

 

[SirJB7]

Hi, umpscott!

And what about this?

    Dim c As Range
    For Each c In Range("DataTable[Pred_Description]")
        With c
            If Right(.Value, 1) = "~" Then .Value = Right(.Value, Len(.Value) - 1)
        End With
    Next c

Regards!

 

[umpscott]

Thanks SirJB7...... I had to make one change. I replaced second RIGHT with a LEFT, as the result was cutting off the first character.

Dim c As Range
  For Each c In Range("DataTable[Pred_Description]")
  With c
  If Right(.Value, 1) = "~" Then .Value = Left(.Value, Len(.Value) - 1)
  End With
  Next c

 

[SirJB7]

Hi, umpscott!
Yes, my mistake, you're right.
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!