Excel (2010) Question (probably trivial)

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.

What about a hidden column with your values say colum C,
then in column D use the formula =+C2&""""


Ian

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.

Two ' should work where " doesn't

Just add the following to any cell formula

&""""

#,##0.00\";[Red]-#,##0.00\"

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

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)

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

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.

Post deleted by camieabz