Is there any conditional formatting programming or plugin you are aware of to make Excel recognize metric quantity and unit suffixes in the same way that Spice does? For example I want to have a column of capacitor values and I want to be able to enter "20μF" for one capacitor and "3F" for another, and so on out to 100+ rows, and have excel recognize these as actual numbers in such a way that I can sort by ascending or descending. Resistor values from mΩ to Ω to KΩ to GΩ, all recognized painlessly.

Also it would be cool (and probably necessary) if the cell formatting would automatically convert to the proper base unit; For example if I entered "20000kHz" it would display "20Mhz."

I have consulted the all-knowing, all-seeing google, and so far the results are not promising. Answers to this question range from "No, it's not possible" to "Here's 1000 lines of VBA code that will allow you to do what you want, so long as you use 3 separate columns to display the same information in different formats."

I figured if anybody knows how to do this gracefully, that person would be a member of this beautiful nerd herd.

 

This seems incomplete; open brackets, comma patterns that I don't understand. I have no idea why it works, but it does (Sorta). As long as you enter "4000000000" it will display "4.0 GHz," but if you enter "4.0 GHz" it will left-justify and be recognized as text instead of a number. Enter into the formatting dialog (number>custom):

[<1000000]0.0, "kHz";[>=1000000000]0.0,,, "GHz";0.0,, "MHz"

I found it online with a comment that it can only be used to display in 3 scales (kHz, MHz, GHz in this case).

I'm still looking for something better, where I can enter "4.0 GHz" and have it be recognized as a number equivalent to "4,000,000,000."

 

Why not organize your data as follows:
- one excel column with values​​;
- another excel column with measurement units.
It's a pretty simple way to organize your data.

If you still want to format the data in this way - for example - typing 100kH and Excel sees it as 100000Hz, then you could call the Custom Format Cell (type: 0.0,kH), if in the same Excel worksheet have one or at most two types of formatting. If you have several types of formatting, then VBA is the best solution.

 

Check out using "SUBSTITUTE"
like

=VALUE(SUBSTITUTE(A1,"k","000"))