Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User cheshire_man
(fountain of knowledge) Mon 24-Oct-11 19:56:25
Print Post

Excel (2010) Question (probably trivial)


[link to this post]
 
I'm trying to get a cell formatted such that it displays as +xx.x" or -xx.x"

In other words the inches sign.

Using special format I can get +xx.xin or -xx.xin but it won't let me have a double quote mark as text. The usual trick of having two quote characters to give one doesn't seem to work in the cell number format.

I'm sure it's simple, but for the life of me I can't see how or get a relevant Google hit.

Tony
Standard User ian007jen
(member) Mon 24-Oct-11 20:08:24
Print Post

Re: Excel (2010) Question (probably trivial)


[re: cheshire_man] [link to this post]
 
What about a hidden column with your values say colum C,
then in column D use the formula =+C2&""""


Ian
Standard User cheshire_man
(fountain of knowledge) Mon 24-Oct-11 20:13:16
Print Post

Re: Excel (2010) Question (probably trivial)


[re: ian007jen] [link to this post]
 
Indeed one way, though, to my mind, messy and inelegant.

It surprises me that Excel allows for &""""& in string concatenation to give a single quote but not in cell formatting.

Tony


Register (or login) on our website and you will not see this ad.

Anonymous
(Unregistered)Mon 24-Oct-11 20:16:21
Print Post

Re: Excel (2010) Question (probably trivial)


[re: cheshire_man] [link to this post]
 
Two ' should work where " doesn't
Standard User camieabz
(sensei) Mon 24-Oct-11 20:16:52
Print Post

Re: Excel (2010) Question (probably trivial)


[re: cheshire_man] [link to this post]
 
Just add the following to any cell formula

&""""

~~~~~~~~~~


© Camieabz 2002-2011

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User 12eason
(eat-sleep-adslguide) Mon 24-Oct-11 20:31:03
Print Post

Re: Excel (2010) Question (probably trivial)


[re: cheshire_man] [link to this post]
 
#,##0.00\";[Red]-#,##0.00\"


Use the escape character \ on any special symbol to use it. Like a regexp.

___________________________________________________________________
           Firenet - V21 - Fast4 - f·2·s - eclipseinternet - entanet - aaisp.net - plusnet
Standard User philippercival
(committed) Mon 24-Oct-11 20:46:23
Print Post

Re: Excel (2010) Question (probably trivial)


[re: camieabz] [link to this post]
 
or format the cell with a custom format of

#,###.00""\""";-#,###.00""\""";0.00;

The only thing I can't solve is the leaving zero on a number like 0.25"

Edit

12 got there while I was playing and his is far more elegant.

Edited by philippercival (Mon 24-Oct-11 20:49:20)

Standard User cheshire_man
(fountain of knowledge) Mon 24-Oct-11 22:08:45
Print Post

Re: Excel (2010) Question (probably trivial)


[re: Anonymous] [link to this post]
 
In reply to a post by Anonymous:
Two ' should work where " doesn't
Strictly that produces 2 single quotes, but neat thinking.
In reply to a post by camieabz:
Just add the following to any cell formula
&""""
But that doesn't then permit the cell to be referred to in another formula (OK, I hadn't actually said I wanted to be able to that grin ).
In reply to a post by 12eason:
#,##0.00\";[Red]-#,##0.00\"

Use the escape character \ on any special symbol to use it. Like a regexp.
That's it, thank you. I didn't want the red and only 1 decimal place but that was trivial to change. It's odd though, I'd tried the backslash as an escape but it didn't seem to work, I suspect I hadn't quite got the format right.

And thank you to philippercival for his input.

All good stuff, many thanks.

Tony
Standard User camieabz
(sensei) Mon 24-Oct-11 22:47:57
Print Post

Re: Excel (2010) Question (probably trivial) *DELETED*


[re: cheshire_man] [link to this post]
 
Post deleted by camieabz
  Print Thread

Jump to