Excel Macro to edit and delete one character in a cell

[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

 

[Jack Sheet]

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.

 

[Harald Staff]

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

 

[Bob Phillips]

or maybe =INT(A1/10)

 

[Sharad]

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

 

[David]

Brilliant. Works great. Thanks.

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