Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    apostrophe replacement ('97, SR-2)

    I have some old XLS files where many cells contain numbers that are preceded by an apostrophe (').
    What is the best way to strip out the apostrophe?
    Search and replace doesn't seem to work.
    Is the =RIGHT() function my best bet?
    (Fortunately all of the numbers are 6 digits long)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: apostrophe replacement ('97, SR-2)

    The apostrophe is not really inclided in the contents. It is used as an indicatore to excel to treat the cell contents as text rather than numeric values.

    If you copy a blank cell, and then select Edit, PasteSpecial and from the Operation section select Add, the cell contents should be converted to numerics, where possible. Whatever formatting applies to the blank cell will be applied to the destination cells.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: apostrophe replacement ('97, SR-2)

    Actually, an easy way is to do this through the "Text to columns" feature.
    <Data> <Text To Columns>
    select "Delimited" click on the "Next " button twice, then for the "Column Data Format" selct 'General'. Click on 'Finish'
    This has the effect of turning text into numbers, but by clicking "Next" twice, you don't actually split the text to columns.

  4. #4
    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: apostrophe replacement ('97, SR-2)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> just clicking <finish> is faster. You don't need to click next twice.

    If you have multiple columns of data, Andrews suggestion is quicker. as you don't have to do it multiple times.
    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
  •