Remove Last x Character(s) in Selected Cells

  • Thread starter Thread starter Guest
  • Start date Start date
One way using the active cell:
ActiveCell.Value = Left(ActiveCell.Value, Len(ActiveCell.Value) - 2)

If you want a specific cell reference:
Range("A1").Value = Left(Range("A1").Value, Len(Range("A1").Value) - 2)

HTH,
Paul
 
One way:

Public Sub RemoveLastXCharacters()
Const cnX As Long = 2
Dim rCell As Range
Dim nLen As Long
For Each rCell In Selection
With rCell
nLen = Len(.Text) - cnX
If nLen <= 0 Then
.ClearContents
Else
.Value = Left(.Text, nLen)
End If
End With
Next rCell
End Sub

Note: If the values are numeric, the last two digits will be "removed",
but the number of displayed digits will still be set by the number
format, so

1.2345

will display as

1.2300

if the format displays 4 digits after then decimal place.
 
Here's an equation to do it in helper column

=left(A1,len(A1)-2)

Do you need it in a macro?
 
This formula works great! But, can you explain/translate the formula?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Removing 2-3 characters at the end 5
find and replace numeric strings in larger text strings 8
Need to remove cells with variable data 3
testing range of cells for part of cell content 5
Find second to last value in cell 4
Excel Macro to automatically go to the right of the last colored cell 0
removing text characters from a cell 12
Limitations on Sumif Calculation 3

Back
Top