1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: breaks single quotes

Discussion in 'Business Applications' started by Zenoxio, Jul 2, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Zenoxio

    Zenoxio Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    389
    I typed this in a field:
    '',

    (two single quotes, then a comma)

    Excel changes it to:
    ',

    (one single quote, comma)

    I don't want it to do this, and I can't figure out why and how to stop it from doing this. Any ideas?
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    A single quote at the start of a cell is used to force text format. If you must have 2 single quotes at the start, create them as follows:

    type 0146 on the Number keypad while holding down ALT.

    HTH.
     
  3. lavazza

    lavazza

    Joined:
    May 15, 2006
    Messages:
    125
    Or...

    type THREE single quotes, and you'll end up with TWO
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Bomb nearly has the answer here, for if you type in 3 single quotes, although you will only see two, if you put your cursor in that cell you will see all 3 in the formula bar at the top. However, what you need to do is type in Alt+0146 followed by ', .
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    ALT+0146 per single quote was what I meant. My bad. :eek:
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hey, one mistake in 3548 posts isn't too bad :)
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
  8. lavazza

    lavazza

    Joined:
    May 15, 2006
    Messages:
    125
    2 arguments against the Alt+0146 approach:

    ''' = 3 key strokes
    ’’ = 10 keys strokes (12 on a keyboard without a 'numeric keypad')


    My eyes AND the underlying logic in Excel (and OpenOffice Calc) say: '' <> ’’
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Technically they are apostrophe's. ;)

    Excel does this to allow the user to assimilate textual data (i.e. a textual formula, number or date) in a calculated atmosphere. If you are first typing an apostrophe you must always assume (because it will) the first apostrophe will force the cell value to be textual, edit: thus the first apostrophe will not show in the cell value, but rather be hidden. For example, compare these two cell entries...

    =TEXT("abcd","@")

    '=TEXT("abcd","@")

    The two will show quite dissimilar results. If you do not know what they would show, put them in two separate cells. (Remember the single apostrophe on the second example.)

    Also, the alt code should be 0039 for the single apostrophe. :)

    (Hiya Bomb/Loche!)
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    I tried 0039 before posting & it still did the "Excel changes it" thing -- for me, anyways.

    Good to "see" you Zack. (y)
     
  11. lavazza

    lavazza

    Joined:
    May 15, 2006
    Messages:
    125
    I take it the ;) was a nod to the grocer's apostrophe

    :p
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    I thought it was pretty witty. :D
     
  13. Zenoxio

    Zenoxio Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    389
    [EDIT] I have a bigger problem. If I use the ALT trick, it doesn't work outside Excel. Excel shows &#8217;', which is fine. But when I copy that exact thing into PuTTy (to insert sql), it shows up as .', which is wrong.

    Old problem:
    Now I'm having trouble. I don't know how, but two single quotes ended up in a bunch of fields. The Fx bar shows ''text', while the spreadsheet shows 'text', so I wanted to use REPLACE to change it to &#8217;text', but it doesn't seem to be working.

    This is what I tried: (where H11 is the cell the text is at)
    Code:
    =REPLACE(H11,1,1,"&#8217;")
    Yet the result of that is ext', and I don't know why. I told it one letter from the start. Instead, it's taking 3 letters from the start (two single quotes and the t)...
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Use the SUBSTITUTE() function instead. Leave out the last syntax (optional).

    I.e. =SUBSTITUTE(A1,"'","")

    HTH
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/591033