List:Custom Number Format

[matt31084]

I am creating a parts list and I want to be able to enter values into a cell and have them formatted in engineering format with engineering labels. I have made a custom format for resistor values so that when you enter 1600 it formats it to 1.6 k and when you enter 1000000 it formats it to 1.0 M. This is important so that if the parts were to be sorted by value, they would be in the correct order. If you were to just enter 1.6 k and 1.0 M and 1.0 k, then sorting them would make them 1.0 k 1.0 M and 1.6 k which is not correct. Now I would like to do the same but for capacitors, which are normally decimal values <1 . For example, 0.000001 would be 1 u and 0.001 would be 1 m and so on. Using the engineering notation format ##0.0E+0 does this, but I want the E+0 to be replaced by the symbol that represents the SI unit. Is there a way to use the custom formatting to do the engineering notation calculation but hide the E+0 part? Or is there some other way to make the custom formatting manipulate the decimal place? I have the conditional part of the custom format so that it recognizes what symbol to use. Any ideas?

Here is the custom format code I used for the resistor values.
[>999999]##0.00?,,"MΩ";[>999]##0.00?," kΩ";##0.00??" Ω"
this code lines up the decimal points as long as the cells are right aligned
Here is what I have for the conditional part for capacitor values. The 0.00 part is what needs to be changed.
[<0.000000001]0.00" pF";[<0.000001]0.00" nF";0.00" µF"

[matt31084]

*bump*

anyone have any ideas or know if this is even possible?

[Steve R]

When i try formatting cells with this line:
[>999999]##0.00?,,"MΩ";[>999]##0.00?," kΩ";##0.00??" Ω"

I get this result:
1.000, kΩ (cellvalue=1000)
1000.000, MΩ (cellvalue=1000000)
0.0900, Ω (cellvalue=900)

Is that result correct?

[matt31084]

no that shouldn't be the correct result. I am using Excel 2000 so that might be the difference. But the ? sign should leave a space for insignificant zeroes but not display them. So I'm not sure why you have the extra zeroes after the decimal point. It also seems that the comma isn't scaling the thousands correctly. Here are the results I get:

1.00 kΩ (cell value = 1000)
1.00 MΩ (cell value = 1000000)
900.00 Ω (cell value = 900)

[shg]

This: [>=1000000]0.?,," MΩ";[>=1000]0.?," kΩ";0.?" Ω"

Give this in 2003:

Please Login or Register  to view this content.

[matt31084]

what are the cell values that you are entering?

[shg]

A1:A3: 10,11,12

A4 and copied down: =A1*10

[matt31084]

oh yeah those values are right, just realized that you changed the format a little lol. Basically does the same thing, mine just has more decimal values. But this isn't what I'm having trouble with! I need to know if there is code that scales by 1/1000 instead of 1000.

[shg]

I need to know if there is code that scales by 1/1000 instead of 1000.

Nope.

For caps, you could enter C in pF and use [>=1000000]0.?,," mF";[>=1000]0.?," uF";0.?" pF"