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.mrexcel.com/forum/showthread.php?p=2779610#post2779610

[MarvinP]

Hi chesspupil,

I think if you have a formula in the cell you are entering the numbers into it won't work. You really want to divide the numbers by 100 or 1000 and then append the double quote mark.

I think you are better off by building a helping column and then copy and paste over the original using values only.

[Palmetto]

I don't think a custom format option is possible for this.
How could format know that 432 should be 0.432 and 132 is to be 1.32?
Both are the same length.

If possible, enter all of the 0.xxx numbers in a column (straight entry, general format)
do the same for all 1.xx numbers.

Afterwards, for all 0.xxx numbers enter a value of 1000 into a blank cell, copy the cell, the highlight the target column (all cells) and perform a Paste Special > Divide.

Do the same for the 1.xx cells but use a factor of 100.
No need for a helper column with this approach.

If you plan of using these values for analysis, then omit the double-quote character which will cause Excel to view the values as text and thereby prevent calculations with the data

[chesspupil]

There are two seperate reports,
For the first one I have a solution:

Drop this into the CUSTOM FORMAT area Without the ( )
("."#[$"])

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

It is only a three digit input

Specifically wanted to avoid the formula solution as the users in my office would eventually mess it up and print the wrong block of data.