Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and replace (2002)

    Hi all,
    I have a column of data that is entered in this type of format (Without the quotes) "1RM UNI M69696".
    What I need to do is do a find and replace to to delete anything before and after the "UNI". The UNI could be anything, but I want to retain the info that is in this placeholder and delete everything else.
    Can this be done with Find and Replace?
    Thanks.

    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and replace (2002)

    I don't think you can do this with Find and Replace in Excel. It would be possible in Word, so you could copy the data to Word, edit them, then copy them back to Excel.

    In Excel, you could use formulas to extract the part you want: say your data are in column A, starting in A1. In B1, enter this monster formula:
    <code>
    =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
    </code>
    and fill down as far as needed. You can now hide column A, or use Copy followed by Paste Special with the Values option to get rid of the formulas, then delete the original data.

    It would also be possible to create a macro to manipulate the data.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (2002)

    Thanks Hans, much appreciated.

    Bill

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (2002)

    Hans,
    What if I wanted to do a Find and Replace and just retaining the first three characters? Can this be done with F and R?
    Thanks.

    Bill

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find and replace (2002)

    Not with Find/Replace

    =left(A1,3)

    will work as a formula

    Steve

Posting Permissions

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