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.

Solved: remove single alignment quote mark excel cell

Discussion in 'Business Applications' started by Forward, Sep 26, 2005.

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

    Forward Thread Starter

    Joined:
    Jul 15, 2005
    Messages:
    77
    Hi
    I am working with a huge spreadsheet
    I need to repeatably copy and paste from some cells to another document
    (saving in another format is not effective for this job)

    The cells that concern me are "text" - some of the cells in that column have been formatted as text and contain the ' - they have the single quote for left alignment
    This is making my cut/paste job quite tedious
    I would like to remove the quote mark so I don't have to not select it on copy or have to delete it on paste

    I have tried selecting the column and making the number and alignment General (have tried many many things) and cannot make them go away.
    I have tried a replace all to no avail

    Is there anyway to globally (throughout this workbook) remove all of the single alignment quote marks?

    Thank you
    Beth
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,072
    When you say you tried a Replace All, you mean you copied the trouble character, opened the Find/Replace dialog, pasted the character into the Find field and left the Replace field blank, and that didn't work?

    Maybe I'm confused because I didn't know of the single quote character acting as an alignment operator...is it possible that the character is a backtick (immediately to the left of the number keystrip) not a single quote?

    Just some questions.

    chris.
     
  3. Forward

    Forward Thread Starter

    Joined:
    Jul 15, 2005
    Messages:
    77
    Yes ... I copied the character and did a Replace All ... although it said it made "x" number of replacements, nothing changed.

    this is a screenshot of what I want to remove ... there is a little tick to the left of the first word.

    I just heard of it being an alignment character when I tried to Google to remove the thing (multiple results came up with that phrase) ...

    Does this clarify?
    Am I looking to remove the "right thing"
    Can I get rid of 'em?


    thanks
    Beth
     

    Attached Files:

  4. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,072
    Is it true that the character only appears in the formula bar (screenshot seems to indicate so, but is a bit fuzzy)? Not only that, but the cell has a text value; the single quote alignment operator (as far as I can Google) only affects number-formatted cells. But you took care of that?

    Instead of using Replace All, try using Find Next...Replace and do them one at a time--I wonder if you didn't just delete a slew of apostrophes.

    I guess my main question is, if it's transparent to the user, why does it matter (besides being an annoyance)? I know that sounds like a smart answer, but sometimes it's easier to ignore weird things like this if they don't affect anything adversely.

    chris.
     
  5. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    Select all the cells that have the (hidden) '
    Edit - Copy

    On another temporary spreadsheet (or elsewhere on that one, in an unused area, Edit - Paste Special - Values
    Then highlight the new pasted cells, and Edit - Copy - Past back over the originals.
    The ' will then be gone. You can then delete the temporary pasted cells.
     
  6. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,072
    Oddly, for numbers, the numbers are still considered text. Formatting the cells doesn't make any difference, either. Only manually deleting the quote mark seems to make the data a number again. That's some odd business. But it does get rid of the single quote.

    [edit]
    Maybe I'm missing something. I'm really tired. Pasting values like kiwiguy said, then copy/pasting those values into a different sheet/book seems to change the numbers back, though.
    [/edit]
     
  7. Forward

    Forward Thread Starter

    Joined:
    Jul 15, 2005
    Messages:
    77
    Yes
    there are hundreds of lines times several sheets - would take forever
    it is in the end result (pasting info into a new database) - I can't have/don't want the quote, and I have to either be very careful on my select or delete them after paste - it is killing my wrist and is very slow - so an annoyance for sure.
    I did try that - formatted the cells as "general", not "text" and tried a special paste - but it didn't work

    BUT after bashing this for way too long, I found an easy, albiet a bit messy way around this.
    = I found a text cell that did not have the quote tick
    = I grabbed the Format Painter
    = I clicked the top of the column that was affected
    = it was done!
    (the messy part was adjusting a dozen or so rows that did not warrant the format change - not so bad)

    Thank you for your interest in helping me
    Beth
     
  8. Forward

    Forward Thread Starter

    Joined:
    Jul 15, 2005
    Messages:
    77
    How to I go about marking this post as solved?

    thanks
    Beth
     
  9. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,072
    Glad you got it sorted, you can use the Thread Tools at the top of the page (above the first post on the right side).

    chris.
     
  10. 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/402227