# Solved: Excel macro to delete last character



## Paul Wit Bard (Jul 26, 2008)

I find my Excel (ver 2003) won't record what I want it to do. 
I paste in a range of data from another programme, at least 2 columns are partially numeric and each cell ends with a 'd' or a '*'. I want to remove the last character to make it purely numeric, so I need a macro that will take the cell my cursor is in, edit it and remove the last character, then move down a row - so i can do it again by running the macro again (I attach the macro to a button). Every attempt to record a macro records the cell I first start with, it doesn't edit the cell where my cursor starts, and it substitutes a specific value that happened to be in the cell where I started.
i.e. I had
23d
34d
56d
and I want 
23
34
56
but the numbers may be any numbers plus a 'd'
Help appreciated, i can't fathom enough Visual Basic to rewrite the macro recorded.
Thanks for the help


----------



## OBP (Mar 8, 2005)

You can have the macro just remove the last Character of the cell that you are on, or if you have a very large number of cells to work with you could have the macro loop through all of the cells for you.
To just remove the last character of the current cell use
ActiveCell = Left(ActiveCell, Len(ActiveCell) - 1)


----------



## DaveBurnett (Nov 11, 2002)

I wrote a macro a few years ago that converts cells with an "overpunched" sign in the last character to the correct value. Is that what you are getting??.

It works on a whole column until it finds an empty cell.


```
'
' convert Macro
' Macro recorded 25/03/97 by Dave Burnett
'
'
Sub convert()
Dim x
Dim y
Dim isit
    While Not IsEmpty(ActiveCell.Value)
        With ActiveCell
            x = Right(.Value, 1)
            isit = Asc(x)
            If isit > 57 Then
                isit = isit - 64
              x = Chr(isit)
              y = .Value
              Mid(y, Len(y), 1) = x
             .Value = 0 - Val(y)
              Rem      .Value = y
                    
             End If
        End With
        ActiveCell.Offset(1, 0).Select
    Wend
End Sub
```


----------



## Rollin_Again (Sep 4, 2003)

A macro is kinda overkill if you ask me. You can always insert a helper column and use a formula instead to determine if the value in the cell is a number and if not to trim the last character off. Then just copy the formula down for all rows. After all your rows have been calculated you can just copy the entire column and paste back as "values" instead of formulas and then delete the original column.

*=IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1))*

If you prefer a macro just let us know. There are several people in this forum including myself that "dabble" with VBA.

Regards,
Rollin


----------



## Paul Wit Bard (Jul 26, 2008)

Thanks, guys, the first solution by OBP works fine, and I'll follow up the others when I surface. Thanks to you all fro the help, I've bookmarked the forum in case I have further problems

Regards, Ppaul


----------

