Re: Macro that deletes the last character in a cell

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Richard, would you be so kind as to explain your use of the $ character
in the term Left$. It appears to be a typecasting character from my
old BASIC days. What are the implications of using the $ character
with Left at run-time? TIA, James

c.Value = Left$(c.Value,Len(c.Value)-1)

RichardSchollar wrote:
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

.

Relevant Pages

  • Re: where does a character appear in a cell?
    ... a typo in my search wasn't helping! ... Is there a function that allows me to calculate where in the cell a ... specific character appears? ...
    (microsoft.public.excel)
  • Re: Rotated Text Help Needed
    ... cell heights as you said happened on your XP. ... > characters such that the top left corner of the character cell sits at the ... > character cell as well as the character glyph itself. ... > Private Const LF_FACESIZE = 32 ...
    (comp.lang.basic.visual.misc)
  • Re: Interesting Observation
    ... it actually gave me the first character of the next table cell! ... the range to cover only the text in a cell without the marker. ... the end of cell marker consists of two characters, ... Greg Maxey/Word MVP ...
    (microsoft.public.word.vba.general)
  • Re: Formatting index entries
    ... replace each space with a tab character ... Copy the whole cell ... use Edit>Replace to replace every tab with a space. ... you want to rip all the Index tags out of the main document. ...
    (microsoft.public.mac.office.word)
  • Re: Carriage return in same cell without using alt-enter
    ... I still think that the OP is underestimating the user community. ... to change this unique character to the alt-enter. ... Here is the code now in the custom Sub: ... I have managed to intercept the ENTER keypress whilst editing the cell with: ...
    (microsoft.public.excel.worksheet.functions)