Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: VBA: Delete the first two characters of a cell

  1. #1
    Join Date
    8th August 2003
    Posts
    14
    I have a set of data that is about 27000 cells long. How do I write a macro that selects a column of data and deletes the first two characters.

    Any help would be greatly appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    A macro can be done, but it might be safer to enter the following in a blank row 1 cell of an adjacent column to the right (or a newly created blank column) the following: assuming the original data are in column A:

    =RIGHT(a1,LEN(a1)-2)

    Then autofill column B by doubled clicking on the small dark square at the bottom right of cell B1 when it is selected. This should autofill column B to the end of Column A entries (assuming they are contiguous) and also leave the column B cells selected. Copy the column B cells and the do a PASTE SPECIAL > VALUES (either in place in column B or over the top of the original data in column A. This should create a new dataset that is minus the first two characters.

    If you still need or want a macro to do this, see the attached. But be warned that the macro will do exactly what you ask -- remove the first two characters from each cell in column A. If you run it a second time, the next two characters will be gone, too. If someone runs it "for" you a week later, an additional two characters will disappear.

    Having a macro around that can do inadvertant harm always bothers me.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete Cells With x Characters
    By Wigi_is_a_god in forum EXCEL HELP
    Replies: 1
    Last Post: August 22nd, 2008, 09:37
  2. Delete row based on first 4 characters of cell
    By austenr in forum EXCEL HELP
    Replies: 5
    Last Post: May 4th, 2006, 21:35
  3. Delete Unwanted Characters
    By biocyberman in forum EXCEL HELP
    Replies: 2
    Last Post: February 13th, 2006, 14:05
  4. Delete first 9 characters from string
    By fraggles in forum Excel and/or Email Help
    Replies: 8
    Last Post: May 23rd, 2005, 19:03
  5. Delete Characters
    By Click69 in forum EXCEL HELP
    Replies: 2
    Last Post: February 18th, 2005, 22:56

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno