Single Quote - Excel 2007 [Solved/Closed]

Posts
2
Registration date
Tuesday December 8, 2009
Status
Member
Last seen
March 24, 2010
-
Hi,

Does anyone know what the single quotation mark ' means when entered at the beginning of a cell?

I notice that the quotation mark does not actually appear in the cell. In other words, it appears in the formula bar when entered but disappears from the cell once you press enter.

I want to to get rid of that. I need a single quote at the beginning!

System Configuration: Windows XP / Safari 532.5
See more 

6 replies

Best answer
approved by Ratnendra Ashok on Jan 31, 2019
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
750
13
Thank you
Hi,

That means the value being enter is text. It is like if you want to write 001 in a cell, generally Excel would convert it to 1 but if you write it as '001 it will stay as 001.

Say "Thank you" 13

A few words of thanks would be greatly appreciated. Add comment

CCM 6597 users have said thank you to us this month

3
Thank you
To get RID of the leading (hidden) sinlge-quote, paste into Notepad, then paste back into Excel but this time "Paste Values" (right-click, Paste, choose the "123" paste option for Values). Works like a charm.
2
Thank you
A single quote in excel is used to maintain formatting of the data enterd in the field. Or better yet, to ignore formatting and read it as text.

For example, if you want to enter ( = $5.00 ), excel will assume you are trying to enter a formula and will return an error. If you wish to display the data as it appears above you must enter a ( ' ) at the begining.

'= $5.00

when you hit enter, the (') will disappear, displaying only the text after the apostrophe

= $5.00

Hope I didn't make that too complicated.
2
Thank you
Or

Type in an additional inverted comma in the beginning. One will be hidden and the additional will be visible.

For example, if you want to type in '500 in your cell, type ''500. Excel will take off one inverted comma and you will see '500 in your cell.

Hope it helps.
1
Thank you
So how do you get rid of that annoying disappearing single quote? Disable 'smart quotes'? I can't find it in excel 2007.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
750 -
if it is in front of a number, you can use text-to-column feature to make it a number
To remove leading apostrophes (') try:

On the Tools menu, click Options. On the Transition tab, clear the Transition navigation keys check box. Note You can hide only codes that have been implicitly added, such as formatting codes. You cannot hide codes that have been explicitly added.
Posts
2530
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 30, 2019
368
0
Thank you
Just type two single quotes.