Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: remove single alignment quote mark excel cell


(!)

Forward's Avatar
Forward Forward is offline   Forward has a birthday soon!
Member with 77 posts.
THREAD STARTER
 
Join Date: Jul 2005
Location: Newmarket Ontario Canada
Experience: Intermediate
26-Sep-2005, 12:41 PM #1
Solved: remove single alignment quote mark excel cell
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
cristobal03's Avatar
Member with 3,072 posts.
 
Join Date: Aug 2005
Experience: Advanced
26-Sep-2005, 01:00 PM #2
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.
Forward's Avatar
Forward Forward is offline   Forward has a birthday soon!
Member with 77 posts.
THREAD STARTER
 
Join Date: Jul 2005
Location: Newmarket Ontario Canada
Experience: Intermediate
26-Sep-2005, 02:09 PM #3
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 Thumbnails
Solved: remove single alignment quote mark excel cell-tickmark.jpg  
cristobal03's Avatar
Member with 3,072 posts.
 
Join Date: Aug 2005
Experience: Advanced
26-Sep-2005, 03:03 PM #4
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.
kiwiguy's Avatar
Member with 17,584 posts.
 
Join Date: Aug 2003
Location: New Zealand
26-Sep-2005, 04:19 PM #5
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.
cristobal03's Avatar
Member with 3,072 posts.
 
Join Date: Aug 2005
Experience: Advanced
26-Sep-2005, 04:33 PM #6
Quote:
Originally Posted by kiwiguy
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.
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]
Forward's Avatar
Forward Forward is offline   Forward has a birthday soon!
Member with 77 posts.
THREAD STARTER
 
Join Date: Jul 2005
Location: Newmarket Ontario Canada
Experience: Intermediate
26-Sep-2005, 06:05 PM #7
Quote:
cristobal03 Is it true that the character only appears in the formula bar
Yes
Quote:
try using Find Next...Replace and do them one at a time
there are hundreds of lines times several sheets - would take forever
Quote:
if it's transparent to the user, why does it matter (besides being an annoyance)
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.
Quote:
kiwiguy Select all the cells that have the (hidden) '
Edit - Copy
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
Forward's Avatar
Forward Forward is offline   Forward has a birthday soon!
Member with 77 posts.
THREAD STARTER
 
Join Date: Jul 2005
Location: Newmarket Ontario Canada
Experience: Intermediate
26-Sep-2005, 06:08 PM #8
How to I go about marking this post as solved?

thanks
Beth
cristobal03's Avatar
Member with 3,072 posts.
 
Join Date: Aug 2005
Experience: Advanced
26-Sep-2005, 09:34 PM #9
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑