Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leading Apostrophe appears in the formula bar but not in the cells
Excel says "These characters (apostrophe) are formatting codes, which
may be displayed in documents created in Lotus 1-2-3 or if you are working with keys that are used for navigation in Lotus 1-2-3." And "To hide the codes except label prefixes, click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box" Unchecking the Transition check box has no effect, although I don't want to hide them, rather disable them. (No idea what a label prefix is). The sheet was created in Excel, I don't have Lotus 1-2-3 or use shortcut keys. What's required is for the cell to display 'xyz', including apostrophes. Entering this either in the formula bar, or the Cell, or in VBA with 'Cell(x,y) =' results in the leading (prepended?) apostrophe disappearing from the cell. It still shows in the formula bar. Entering two apostrophes goes some way to a fix, the first one vanishes and the second one remains. But there's something intuitively wrong when you have to enter a character twice, just to see it once. Besides, there are some cells where a leading apostrophe DOES appear in both the formula bar AND cell, but I can't see what's different with them, or copy that formatting somewhere else. But it does prove what I want, can be achieved - somehow. Does anyone know what's going on, or alternately know somewhere else I could ask? Thanks - Kirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leading Apostrophe appears in the formula bar but not in the cells
Unless you have a very good reason to enable them, you should always
have the transition settings turned off. In Excel, the single apostrophe (') is an indicator to Excel that the data that follows is literal and should not be translated. The apostrophe does not show in the cell. For example, if you enter 5/3/2008 in a cell, Excel translates to a date value. If you enter '5/3/2008, Excel leaves it as it is and does no translation. Since the apostrophe is not shown in the cell, you must use two apostrophes to display one leading apostrophe. For example, enter '5/2/2008 displays as 5/2/2008 ''5/2/2008 displays as '5/2/2008 Note that in the second example, the value leads with two single apostrophes, not a single double quote character. In general, if the first character entered in a cell is an apostrophe, that apostrophe will not be displayed in the cell but everything else, including subsequent apostrophes, will be displayed. In the formula bar, the leading apostrophe is displayed so that you know it is there and so that you can remove it if desired. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 05 Nov 2008 11:30:24 +1300, kirkm wrote: Excel says "These characters (apostrophe) are formatting codes, which may be displayed in documents created in Lotus 1-2-3 or if you are working with keys that are used for navigation in Lotus 1-2-3." And "To hide the codes except label prefixes, click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box" Unchecking the Transition check box has no effect, although I don't want to hide them, rather disable them. (No idea what a label prefix is). The sheet was created in Excel, I don't have Lotus 1-2-3 or use shortcut keys. What's required is for the cell to display 'xyz', including apostrophes. Entering this either in the formula bar, or the Cell, or in VBA with 'Cell(x,y) =' results in the leading (prepended?) apostrophe disappearing from the cell. It still shows in the formula bar. Entering two apostrophes goes some way to a fix, the first one vanishes and the second one remains. But there's something intuitively wrong when you have to enter a character twice, just to see it once. Besides, there are some cells where a leading apostrophe DOES appear in both the formula bar AND cell, but I can't see what's different with them, or copy that formatting somewhere else. But it does prove what I want, can be achieved - somehow. Does anyone know what's going on, or alternately know somewhere else I could ask? Thanks - Kirk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leading Apostrophe appears in the formula bar but not in the cells
You could also use this:
="'xyz'" which replaces the extra single quote with two double quotes and an equals sign. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ <kirkm wrote in message ... Excel says "These characters (apostrophe) are formatting codes, which may be displayed in documents created in Lotus 1-2-3 or if you are working with keys that are used for navigation in Lotus 1-2-3." And "To hide the codes except label prefixes, click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box" Unchecking the Transition check box has no effect, although I don't want to hide them, rather disable them. (No idea what a label prefix is). The sheet was created in Excel, I don't have Lotus 1-2-3 or use shortcut keys. What's required is for the cell to display 'xyz', including apostrophes. Entering this either in the formula bar, or the Cell, or in VBA with 'Cell(x,y) =' results in the leading (prepended?) apostrophe disappearing from the cell. It still shows in the formula bar. Entering two apostrophes goes some way to a fix, the first one vanishes and the second one remains. But there's something intuitively wrong when you have to enter a character twice, just to see it once. Besides, there are some cells where a leading apostrophe DOES appear in both the formula bar AND cell, but I can't see what's different with them, or copy that formatting somewhere else. But it does prove what I want, can be achieved - somehow. Does anyone know what's going on, or alternately know somewhere else I could ask? Thanks - Kirk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leading Apostrophe appears in the formula bar but not in the cells
Chip, thanks for the run down which makes
sense and it what (mostly) happens. But I *DO* have some instances when one leading apostrophe exists and displays as such in both the the Cell and formular bar. I'm hoping to find how this happened and how can I do it again. It's very irritating because it's exactly what I want. The sheet's use is for a static text display with no formulas, calculations or fancy Excel things. I use the formual bar for editing and data entry (mainly to avoid that icon thing that otherwise shows up) and it is just plain wrong for it to be different to the cell. I fel I'm in a losing battle though... ! Thanks - Kirk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leading Apostrophe appears in the formula bar but not in the cells
On Tue, 4 Nov 2008 17:51:15 -0500, "Jon Peltier"
wrote: You could also use this: ="'xyz'" I tried that, and found the quotes and equal chars displayed in the Cell as well. Unless I did something wrong? Cheers - Kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Apostrophe | Excel Discussion (Misc queries) | |||
Leading Zero & Apostrophe - Help | Excel Discussion (Misc queries) | |||
apostrophe and leading zeros | Excel Discussion (Misc queries) | |||
Leading Apostrophe | Excel Worksheet Functions | |||
iNSERT LEADING APOSTROPHE | Excel Discussion (Misc queries) |