Sorting in electronics way

[bonny]

Hello…
I’m struggled how to sort values in calc. The problem is that I want to sort values of resistors and they are written such there are no decimal point but value nominator instead.
So instead 4.7 kiloohm the 4k7 is written.
The letters are R(esistance), k(ilo), M(ega), G(iga)

so how to sort to get values in correct order? Sorting in natural way is close but doesn’t properly manage R/k/M/G letters
There is example for correct ordering I wish to get.

4R7
22R
47R
68R
4k7
220k
4M7
4G7

[Villeroy]

103086.ods (18.7 KB)

[Lupp]

Finally a recent comment to the answer below reminded me of a demo I made nearly 6 years ago in pursuit of a question in a different forum. I then didn’t use a lookup table but a pair of “lookup strings” for the conversion from “numericText/space?/SI prefix” to ordinary number (‘Double’) in Calc.
Of course the conversion must also be actually performed using one cell per value. It can’t be done on te fly while sorting using the standard tool.
You may check if that old solution is appropriate for you:
aoo94262ordersOfMagnitudeAbbreviated_2.ods (24.1 KB) from this thread.
(I did not check that old solution again, and I personally never used it since. It should work, however.)

[fpy]

you can adapt from this one :

Suggestion: Format numers in Calc Cell by Computer Units

Welcome Kim! Perhaps for your purposes it will be enough to still store the value as an integer number of bytes, so that later you can perform actions with them (for example, sum or find the average), but somewhere nearby, in the next cell, see them as an integer or double kilo-mega -terabyte? Perhaps such a UDF will do just that? Option Explicit Function ConvertBytes(inputBytes As Double, Optional digitPlaces As Integer) As String Dim sTargetFormat As String Dim nPower As Integer Dim aMe…

[bonny]

I asking for sorting table not the fromatting of text.

[fpy]

yep.
so you have the function to extract from your notation (RkgM) the numerical values, which can then be sorted just normally.

[PKG]

• Create your own sort list first (Tools → Options → LibreOffice Calc → Sort Lists)

- Then create an auxiliary column, extract the letters and sort by them.

[bonny]

enable natural sort need to be checked too.
This works. But is it possible without another column?

[fpy]

@bonny

without another column

you have this other option, which would require then to actually enter your data in numerical form :

MB and GB Hidding zero's

Got the answer here: http://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=60863&p=269361#p269361 If your data are numeric and if they are entered with the same unity (MB), Format > Cell, define this format : [>1000000]0.000,," TB";[>1000]0.000," GB";0.000" MB" Note there is one space between 0.000 and " GB" and 2 spaces between 0.000 and " TB". (of course no space for MB) For French locale [>1000000]0,000 " TB";[>1000]0,000 " GB";0,000" MB"

and for reference : 36864 – Calc cell format for Bytes

[keme1]

@bonny

is it possible without another column?

Probably not.

Calc will not directly interpret the component value encoding as numeric, so some way or other you need to convert to actual numerical value or some entity which sorts equivalently.

I attached a file showing how I might do it. Three extra columns, no less. You could make that a one-liner (so you’d only need one extra column), but that would be fairly convoluted I guess.

Also with multiple encodings I might use a lookup table instead of the inline string and the CHOOSE function.

if you need to do calculations on your electronics projects, the actual values are also useful. The table can be used for lookups.

The second sheet is added for robustness, in case your decimal separator is not the same as mine. If you don’t need to cater for different locales, you can simplify a bit.

ResistorEncoding.ods (11.7 KB)

[bonny]

so far [PKG] solution is best for now. Maybe if I reformat input column to decimal (ie 2.7k instead 2k7) is it possible to use standard (decimal) prefixes to do sort?

[Lupp]

@bonny

This works. But is it possible without another column?

Struggling for single-column solutions isn’t necessary. Sheets have enough columns to give room for a well structured and maintainable solution. You easily can hide these columns - and show them again as soon as you need them.
See:
ResistanceMess.ods (27.0 KB)

…
When I first found this funny way to list resistors with their values in that messed-up notation 50 years ago I thought of a bad joke which soon would be over. Wrong! Now I know that rarely something is as log-lived as specially bad ideas.

@bonny

… is it possible to use standard (decimal) prefixes to do sort?

• Standard prefixes are prefixes to SI units.
• They are not standardized as postfixes to numbers.
• No they are not supported by a default feature.
• If you need them often it may pay to write custom code for the interpretation.
• There are no means to get them into a number format described by an ordinary code.

[EarnestAl]

@Lupp

that messed-up notation 50 years ago I thought of a bad joke which soon would be over.

It gives error prevention for physical components that are readily marked. A decimal point (or comma) is easily missed or scraped off or inserted by a speck of dirt.
Of course it isn’t the only method, the coloured rings on small components don’t use characters at all

Neither method was intended for cataloguing, although for untrained people a list of the actual markings make stocktake and fulfilling orders less error prone.

[bonny]

If using additional columns then probably simplest way should be to convert funky notation to decimal value and then sort that.

[fpy]

@bonny

simplest

[bonny]

So this is my solution.
REGEX to replace any letter into decimal.
multiply by 1000 if k is found or multiply by 1000000 if M is found, otherwise multiply by 1 if not K or M.
I just wonder if formula can be even shorter. (I’m really ocassional user of libreoffice)

current formula is:
=REGEX(A2,"\D",".","g")*IF(IFERROR(FIND("k",A2),0),1000, IF(IFERROR(FIND("M",A2),0),1000000,1))

[fpy]

@bonny

=REGEX(A2,"\D",".",“g”)

can have only 1 letter ? so "g" is superfluous.

@bonny

IF(IFERROR(FIND(“k”,A2),0),1000, IF(IFERROR(FIND(“M”,A2),0),1000000,1))

too short since “G” 1000000000 is missing

this could almost make it :
=1000^SEARCH( REGEX(A2, "\d","", "g"), "KMG", 1)

then

[bonny]

And one line/column:
=(REGEX(A2,"\D",".")) *(10^(3*(FIND(REGEX(A2,"\d","","g"),"RkMG",1)-1)))
I replaced SEARCH with FIND to be case sensitive. So any letter other than RkMG show error.
Thanks for help

[Villeroy]

This version works with any locale and with restrictive settings on formula syntax: =NUMBERVALUE(REGEX(A1;"\D";".");".") *NUMBERVALUE((10^(3*(FIND(REGEX(A1;"\d";"";"g");"RkMG";1)-1)));".")

[karolus]

@Villeroy

This version

or this:

=NUMBERVALUE(REGEX(A1;"\D";".");".")*1000^(FIND(REGEX(A1;"\D");"RkMG")-1)