+ Reply to Thread
Results 1 to 5 of 5

Find and Delete character based on criteria

  1. #1
    Registered User
    Join Date
    04-17-2010
    Location
    Chula Vista, California
    MS-Off Ver
    Excel 2007
    Posts
    8

    Find and Delete character based on criteria

    Hi.
    Here's a tough one for you to figure out and for me to explain it.
    I need to search a column for a particular character and delete only that character. Then, I need to sort the whole worksheet and add the deleted string back into the cell.
    Example:
    The column contains:
    R 12345
    R 34567
    23456
    R 23456
    45678
    11111

    I want to remove the R and then sort the column.
    After sorting, put the R back in so it would look like this:
    11111
    R 12345
    23456
    R 23456
    R 34567
    45678

    The column contains many different text, including RGY, JR, so focusing on the R didn't work for me. Currently, I remove the R from the string and put it in another column. Then I sort and afterwards put the R back in the string. I'd like to automate that.
    Hope I explained that properly. I have to do this with other strings where the R is not at the beginning, though there is always a space after it, for example, [GN] R 12345.
    Thanks for your help.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Find and Delete character based on criteria

    Hello cosber,

    Can you post a sample workbook? It will speed up developing and testing a solution.

    To Post Your Workbook
    1. At the bottom right of the Reply window, Click the button Go Advanced
    2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
    3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
    4. Click the Select Files button at the bottom middle of the dialog.
    5. In the new window Find the file you want to upload, select it, and Click Open.
    6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
    7. Wait until the file has completely uploaded before you exit the File Manager dialog.

    File Manger Pic
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    San Francisco
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,163

    Re: Find and Delete character based on criteria

    Try this. The macro removes all texts so you don't have to worry about specific letters.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Find and Delete character based on criteria

    try the macro!!! Happy holidays
    Attached Files Attached Files

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,488

    Re: Find and Delete character based on criteria

    Hi,

    Or a non VBA solution....

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which is something Ron Coderre came up with about three years ago.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1