# Solved: remove single alignment quote mark excel cell



## Forward (Jul 15, 2005)

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 (Aug 5, 2005)

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 (Jul 15, 2005)

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


----------



## cristobal03 (Aug 5, 2005)

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 (Aug 17, 2003)

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 (Aug 5, 2005)

kiwiguy said:


> 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
> ...


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 (Jul 15, 2005)

> cristobal03 Is it true that the character only appears in the formula bar


 Yes


> try using Find Next...Replace and do them one at a time


 there are hundreds of lines times several sheets - would take forever


> 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.


> 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 (Jul 15, 2005)

How to I go about marking this post as solved?

thanks
Beth


----------



## cristobal03 (Aug 5, 2005)

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.


----------

