Excel Macro to edit and delete one character in a cell

  • Thread starter Thread starter David
  • Start date Start date
D

David

Can anyone please help.

I am trying to write a very simple macro that will just edit a cell and
delete the last chracter.
Example:
I have a column with the following numbers:
1234
5678
9032

I want a macro that I can just convert each cell to the following:
123
567
903

Therefore, stripping the last digit. I don't even mind if it splits the cell
into two so the end result is:
123 4
567 8
903 2

Thank,
David
 
You sure you want to do this by macro?
The formula
=LEFT(A1,LEN(A1)-1)
or similar will do this (may have to introduce some error trapping IF
statement to cater for LEN(A1) being fewer than 2 characters long).
If you only have to do it as a one-off exercise, you could create a column
of the above and copy down, then copy the column and pastespecial Values
into where you want it to go, then delete the formula column.
 
Hi David

Select the cells and run this:

Sub DelLast()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If Len(Cel.Value) > 1 Then _
Cel.Value = _
Left(Cel.Value, Len(Cel.Value) - 1)
End If
Next
End Sub

HTH. Best wishes Harald
 
Assuming the number is in Cell A1, in Sheet1:

Sub delLastFellow()
Dim myString As String

myString = Worksheets("Sheet1").Range("A1").Value

myString = Left(myString, Len(myString) - 1)

Range("A1").Value = myString
End Sub
 
Brilliant. Works great. Thanks.

Harald Staff said:
Hi David

Select the cells and run this:

Sub DelLast()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If Len(Cel.Value) > 1 Then _
Cel.Value = _
Left(Cel.Value, Len(Cel.Value) - 1)
End If
Next
End Sub

HTH. Best wishes Harald
Click to expand...
 
Back
Top