custom format help (leading decimal pt and Inch mark)

[chesspupil]

Using Excel 2007
I am trying to enter thousands of thickensss readings that were recorded manually and must be transcribed over to excel/

I have numbers like 432 433 435 431 444 Each value in its own cell

Would like the result

0.432" 0.433" 0.435" 0.431" 0.444

I would like to do this where I only enter the digits and the format results in the same cell rather than a formula target to a new result cell

I imagined this might work for the first set of number, but no [$ 0.]#0[$ "]
--------------------------------
Not in the same report I have a similar problem:

I also have some data that is 132 133 135 136 130

Would like to have result:

1.32" 1.33" 1.35" 1.36" 1.30"

Thank you for your time


Cross post

http://www.excelforum.com/excel-general/783139-custom-format-help-leading-decimal-pt-and-inch-mark.html#post2558349

 

[Greg Truby]

I couldn't figure out a way to get a custom format that would take a normal double-quote that you get when you hit the key. BUT, how about the "fancy" double quote the ” instead of the ". For that one just set a number format like:

0.000 " ”"

To get the "fancy" double-quote, hold down your ALT key and type 0148 using the numeric keypad on your keyboard.

Regarding your second item: AFAIK, you cannot do that kind of conversion w/o VBA. However if you're just thinking about keying in quicker, go to the Advanced group in the Options dialog and the second item is Automatically insert a decimal point and set as desired.

If that doesn't do the trick, then you might key it all and then put 100 in some cell and then do a copy and PasteSpecial and use the DIVIDE operation (and use VALUEs option to keep from bolloxing your formats).

 

[chesspupil]

There are two seperate reports,

For the first one I have a solution:

Without the ( )

("."#[$"])

Still not sure how to get the second one to work....

It is only a three digit input

 

[Greg Truby]

Thank you for posting that back. FWIW, you don't need the quotes around the decimal:

.# [$"]

will work. As for the other, as I said above, if you are handkeying the data, then you can input them using the Automatically insert a decimal option. If it's being imported or copied and pasted, use the PasteSpecial | Divide tool.

 

[RoryA]

As posted at OzGrid:
0\.00\"
should do the formatting.

 

[Greg Truby]

Ah, there it is! The backslash. I knew there was a way, but it'd slipped my mind and the dollar sign was not ringing any bells for me. Thanks for the reminder, Rory.

@ Chess pupil - do be aware that using Rory's format the value of the cell doesn't change. So while the cell shows 5.43 ", the value is still 543

 

[chesspupil]

Thanks, problem solved I appreciate your time


I normally do not do any calculations with the numbers afterwards but with the amount of time this saved us I would nto be suprised if I get an engineer wanting the results in electronic format as well as my printed report.

Is there a way to format the cell though so if a formula was applied that it would read the value as shown?

i.e

5.34" shown is calculated as 5.34" and not 534?

 

[Greg Truby]

Is there a way to format the cell though so if a formula was applied that it would read the value as shown?
i.e
5.34" shown is calculated as 5.34" and not 534?

The only built-in function that I know that returns format info is the CELL() function. But with a custom function like this it is not going to prove reliable. Therefore, I would say it would take some complicated VBA or a UDF to kluge together a workaround.

I cannot overstate how bad of voodoo it is to have cells display one value while containing another. People will copy and paste this data from one worksheet to another or otherwise use it downstream in ways you cannot foresee today and somebody somewhere is going to forget the cells' formatting trickery and somewhere a report is going to be off by two orders of magnitude. Bad, bad voodoo that you do not want associated with your name. I would never put this in any worksheet I was building. If someone else was asking for it, I'd talk them out of it.

A better solution would be to use VBA to actually just go ahead and divide the cells by 100 when values are entered.