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

    Deleting specific text (2002)

    Hi all,
    I am having difficulty with a excel file I am working on. The text below is an example of text strings in the file.

    429,56895%_AREA_DAQ4_101Collins_OD_IB220306.MAP
    16595%_AREA_DAQ4_101Collins_OD_IB220306.TAB
    9295%_AREA_DAQ4_120Collins_OD_Cut230206.DAT
    895%_AREA_DAQ4_120Collins_OD_Cut230206.ID
    549,88895%_AREA_DAQ4_120Collins_OD_Cut230206.MAP
    16595%_AREA_DAQ4_120Collins_OD_Cut230206.TAB


    If you look at the text you will notice that there is a string of text in every line containing the string "95%". What I need to do is delete all characters to the left of every instance of "95%" (no quotes), but retaining the 95% and everything else.
    Is this possible? I tried using Find and Replace but had no luck.
    Much appreciated.

    Bill

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

    Re: Deleting specific text (2002)

    One possibility is to use formulas. Say that your data are in column A, starting in A1.
    In B1, enter the formula
    <code>
    =MID(A1,FIND("95%",A1),1000)
    </code>
    This will return the text you want (the 1000 is an arbitrary number greater than the maximum length of the strings).
    Fill down as far as needed.
    You could hide column A now. Or copy the cells in column B to the clipboard, then Edit | Paste Special with the Values option. This removes the formulas; you can now safely delete column A.

    Another possibility is to run a macro. The following again assumes that the data are in column A.

    <code>Sub Fix95()
    Dim oCell As Range
    For Each oCell In Range(Range("A1"), Range("A1").End(xlDown))
    oCell = Mid(oCell, InStr(oCell, "95%"))
    Next oCell
    End Sub</code>

  3. #3
    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: Deleting specific text (2002)

    You can put this formula in a cell:
    <pre>=MID(A1,FIND("95%",A1),LEN(A1))</pre>


    Copy it down the column.

    You can then copy and paste values this column over the original and then delete the formulas.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>If you want what is to the left of the 95%:
    <pre>=LEFT(A1,FIND("95%",A1)-1)</pre>


    Steve

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Deleting specific text (2002)

    Unless Excel 2002 operates differently, the Find and Replace should work. Find "*95%" and replace with "95%". Everything to the left of the 95% will be gone. This may not help if "95%" occurs more than once in any string.

    Aloha,
    JohnJ

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

    Re: Deleting specific text (2002)

    Thanks all, Ended up going with Hans' solution which worked perfectly as usual.

    hanks again.

    Bill

Posting Permissions

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