[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Macro that deletes the last character in a cell

TJ

12/19/2006 9:04:00 PM

I have an 18,000 record database and every entry is a name with the
letter A added to the end. for example: Lastname, firstname A
. I need a macro that will open the cell, backspace the "A" out of
there, close the cell for editing, moves down one record or line and
loops back to editing the cell and removing the A. This was simple in
Lotus but I'm not a programmer and I really don't want to spend 20
hours hitting, F2, Backspace, enter. Does anyone have a simple
solution. I do know how to set up a macro in Excel but that's about it.
Thanks ...TJ
3 Answers

john

12/19/2006 9:16:00 PM

0

On 19 Dec 2006 13:03:59 -0800, "TJ" <terry.jackson2@nashville.gov>
wrote:

>I have an 18,000 record database and every entry is a name with the
>letter A added to the end. for example: Lastname, firstname A
> . I need a macro that will open the cell, backspace the "A" out of
>there, close the cell for editing, moves down one record or line and
>loops back to editing the cell and removing the A. This was simple in
>Lotus but I'm not a programmer and I really don't want to spend 20
>hours hitting, F2, Backspace, enter. Does anyone have a simple
>solution. I do know how to set up a macro in Excel but that's about it.
>Thanks ...TJ

Rather than use a macro, I'd just do something like this:

Assuming cell A1 is
Jones,BobA

off to the right somewhere put

=LEFT(A1,LEN(A1)-1)

This will drop the last character in the cell.

Then copy down the column as needed, then copy it all and paste
special values over the original data and delete the new column.

John

Zone

12/19/2006 9:18:00 PM

0

How about this?
Sub TryThis()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If VarType(cell) = vbString Then
cell = Left(cell, Len(cell) - 1)
End If
Next cell
End Sub
James

TJ wrote:
> I have an 18,000 record database and every entry is a name with the
> letter A added to the end. for example: Lastname, firstname A
> . I need a macro that will open the cell, backspace the "A" out of
> there, close the cell for editing, moves down one record or line and
> loops back to editing the cell and removing the A. This was simple in
> Lotus but I'm not a programmer and I really don't want to spend 20
> hours hitting, F2, Backspace, enter. Does anyone have a simple
> solution. I do know how to set up a macro in Excel but that's about it.
> Thanks ...TJ

Richard Schollar

12/19/2006 9:18:00 PM

0

TJ

The following assumes records start at row 2 (allowing header in row 1)
and that the names with the A on the end are in column A.

Sub Test()
For Each c in Range("A2:A" & Range("A65536").End(xlUp).Row)
c.Value = Left$(c.Value,Len(c.Value)-1)
Next c
End Sub

Stick this in a standard module in the workbook containing the data
(Alt+F11 to get the VBE open, right click on the workbook name in the
Projects pane (top left) and go Insert>Module and post the code in the
big white space that opens). Back in the spreadsheet, on the sheet
with the records, go Tools>Macro>Macros and select Test from the list
and click Run.

Hope this helps!

Richard


TJ wrote:
> I have an 18,000 record database and every entry is a name with the
> letter A added to the end. for example: Lastname, firstname A
> . I need a macro that will open the cell, backspace the "A" out of
> there, close the cell for editing, moves down one record or line and
> loops back to editing the cell and removing the A. This was simple in
> Lotus but I'm not a programmer and I really don't want to spend 20
> hours hitting, F2, Backspace, enter. Does anyone have a simple
> solution. I do know how to set up a macro in Excel but that's about it.
> Thanks ...TJ