+ Post New Thread
Results 1 to 7 of 7
Windows Thread, Extra apostrophes in Excel in Technical; Ok strange one here. For one user (a fiddler) an XML sheet opened with excel shows apostrophes before just about ...
  1. #1

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,286
    Thank Post
    225
    Thanked 405 Times in 302 Posts
    Rep Power
    162

    Extra apostrophes in Excel

    Ok strange one here. For one user (a fiddler) an XML sheet opened with excel shows apostrophes before just about every cell value. Anyone else looking at the sheet doesn't seem them. So anyone else will 5a, he will see '5a. Now he has his own startup Excel sheet but deleting that didn't stop it. Doing a google search just seems to bring up how to remove extra characters from a badly imported sheet. Ohh and the original XML sheet doesn't have them in.

    Any one any ideas?

    EDIT: Ohh the other thing is it only shows in the formula bar. Not in the actual cell
    Last edited by TechMonkey; 30th April 2008 at 12:52 PM.

  2. #2

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    10,702
    Thank Post
    1,784
    Thanked 2,169 Times in 1,604 Posts
    Rep Power
    769
    That single quote is saying the cell contents is text - you can type '123 (say) into a cell and Excel will treat ist as text rather than as a number.

    I will have a hunt round to see why it is being displayed... it must be an option somewhere.

  3. #3

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,286
    Thank Post
    225
    Thanked 405 Times in 302 Posts
    Rep Power
    162
    Ta, I've had a look at all the options I can think of and nothing jumps out. It has to be somethign turned on though as I've just noticed that header cells that have the title vertically have the ^ before them as well. Grrrrr, I hate fiddelers that don't know how to fix things they do!!

  4. #4

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,286
    Thank Post
    225
    Thanked 405 Times in 302 Posts
    Rep Power
    162
    Found it. Took much hunting down. It is text alignment prefix characters found under
    (Tools > Options)(Transition tab, "Transition Navigation Keys").

    Grrrrrrr, sorted

  5. #5
    jmcdermott's Avatar
    Join Date
    Feb 2008
    Location
    Cornwall
    Posts
    171
    Thank Post
    16
    Thanked 42 Times in 34 Posts
    Rep Power
    21
    a quick fix would be in an empty col enter the formula
    =RIGHT(A1,(LEN(A1)-1))
    assuming col 'A' is the col you want to remove the first char from.

    fill down and you should be done.

  6. #6

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    10,702
    Thank Post
    1,784
    Thanked 2,169 Times in 1,604 Posts
    Rep Power
    769
    Quote Originally Posted by TechMonkey View Post
    Found it. Took much hunting down. It is text alignment prefix characters found under
    (Tools > Options)(Transition tab, "Transition Navigation Keys").

    Grrrrrrr, sorted

    Well done, I've bookmarked this thread as I'll never find it when it happens to someone here!

  7. #7

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,286
    Thank Post
    225
    Thanked 405 Times in 302 Posts
    Rep Power
    162
    Quote Originally Posted by jmcdermott View Post
    a quick fix would be in an empty col enter the formula
    =RIGHT(A1,(LEN(A1)-1))
    assuming col 'A' is the col you want to remove the first char from.

    fill down and you should be done.
    Ahhhh, but when you delete the characters they reappear as it is linked to the formatting not the actual content. Well as far as I can see. It looks like it is some obscure remnant of Lotus 1-2-3 and compatibilty options MS have left in for those changing over. You end up having to either have a ' for left align, a ^ for center align or " for right align.

SHARE:
+ Post New Thread

Similar Threads

  1. Adding extra HD to server
    By mmoseley in forum How do you do....it?
    Replies: 4
    Last Post: 12th October 2007, 05:44 PM
  2. Installing extra languages
    By danIT in forum Windows
    Replies: 1
    Last Post: 23rd May 2006, 09:20 AM
  3. Extra Registry Settings in GPO
    By Geoff in forum Windows
    Replies: 8
    Last Post: 23rd January 2006, 03:24 PM
  4. How much extra time do you put in out of hours?
    By beeswax in forum General Chat
    Replies: 12
    Last Post: 10th January 2006, 09:38 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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