+ Reply to Thread
Results 1 to 4 of 4

Importing data to excel - with an apostrophe?

  1. #1
    Registered User
    Join Date
    04-15-2011
    Location
    Northampton, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Importing data to excel - with an apostrophe?

    Hi
    Newbie here - so I hope this gets posted ok, I am a frequent user of excel and at reasonably knowledgable level.
    When importing data into excell, each cell has an apostrophe before the data even though the apostrophe was not on the original text.
    If you look at the text in the cell, you cannot see the apostrophe, but if you look at the same text in the function bar you can see it. Bizarre! This affects all text, be it numbers, dates, text, postcodes etc. I have hundreds of rows and several columns so there are thousands of cells. I want to get rid of the apostrophe without editing individual cells.
    In the cell the text will appear like this: Surrey
    But in the function bar it will appear like this: 'Surrey
    The data is imported from a non excel database.
    can anyone help?

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Importing data to excel - with an apostrophe?

    NiscayahSteve,

    WIthout a macro, I could only remove leading apostrophes from numbers, but not text. In order to get it to happen on text also, I used the following macro:

    Please Login or Register  to view this content.


    Notes:
    -Before running the macro, highight everything in the worksheet (CTRL+A once or twice should highlight it all)

    Hope that helps,
    ~tigeravatar

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: Importing data to excel - with an apostrophe?

    just put 0 in a spare cell
    copy
    select all your data
    paste special/add
    or when importing make sure the wizard sets the cells as general not text
    Last edited by martindwilson; 04-16-2011 at 10:13 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    04-14-2011
    Location
    Indonesia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Importing data to excel - with an apostrophe?

    1 select all cells at old w/sheet then copy
    2 insert new w/sheet, select cell a1, then paste special value
    3 delete old w/sheet

    hopefully it works

+ 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