Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Leading Quote Mark (Excel 2003)

    I have a client who imports text into Excel from a comma delimited file. One of the columns has text with the single-quote mark around it -- 'text'. When viewed in Excel, the first quote mark - the one at the beginning of the text - doesn't display. It's there as viewed in the formula bar, it just doesn't display in the cell.

    The client needs the quote to display in the cell. He has been entering a space before the quote mark. Is there an easier way for Excel to display the leading quote?

    Thanks,
    Craig.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading Quote Mark (Excel 2003)

    Excel uses a single quote as the first character in a cell to indicate that the following characters are text and does not display the leading quote. That way, you can enter '00005 and have it be the text string 00005.

    Change the first quote to two single quotes like this:

    ''Text'

    and it will display as

    'Text'
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading Quote Mark (Excel 2003)

    Thanks for the response Legare. When I change the leading ' to ", the cell displays "text'.

    What my client is looking for is a quick way to force Excel to display the leading '. I need to check back as to why he needs both quotes to display. First I recommended he replace ' with nothing, thereby elimating all the quotes. But he wants 'text' to display in the cell for some reason. He feels there must be a faster way than changing each cell individually.

    Craig.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Leading Quote Mark (Excel 2003)

    Try typing the single quote twice rather than changing the single to a double.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Leading Quote Mark (Excel 2003)

    Hi,

    I presume the string 'Text' is not constant but rather variable which is a problem which Excel will not address without code.

    The solution is to use Word!!

    Select the cells with the 'Text' data and copy and paste to a blank Word document - the leading quote will be missing.

    Then Select the table in the Word Document

    Select Edit Replace and in the Find what box enter (*') ignoring the brackets (actually single quote and star)

    In the Replace with box enter ('^&) ignoring the brackets (actually - single quote plus Find What text)

    Tick the Use Wildcards box.

    Select Replace All - a single quote will be added to the front of each table cell.

    Select the table and copy back to Excel and you will find the text now appears as required with a leading quote!

    Good Luck!

    Peter Moran

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading Quote Mark (Excel 2003)

    It looks like you replaced the single quote with a double quote. You need to enter two single quotes. Place the cursor in front of the existing single quote and press the single quote.
    Legare Coleman

Posting Permissions

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