How to sort different types of numbers, ie, 1,2,2.25,5.07,10x7,5x3,9,14 - is it possible and if so how?

[spider]

Here is an example of the various numbers in column A:
5.00
6.00
7.00
7.00
7.00
7.00
10.2x4.2x2.9
10.5x10x6.3
10x7
10x7
10x7
10x7
10x7
10x7
10x7
10x7
10x7
10x7
11.5x10.8x6.8
11.5x10.8x7.6
12.0x9.5x6.6
12x8
14x10
14x10
18x13
2.5-3
5x3
5x4
5x4
6.03x5.42x4.14
6.5x5
6X4
6x4
1
2
3
9
10

[Lupp]

Would you mind to explain why something like 10.5x10x6.3 is listed as a number? How should it be treated? Please explain everything of the kind.

[spider]

Yes, actually these are all gemstone sizes so some are round numbers ie 3 or 10, whereas some are 3.25 etc and others, which are more complicated are 10x7 or 10x6.3. I am ‘trying’ to have a list of my gemstones so I could then sort them by their size so in the case of the sizes I have used here would sort to - 3, 3.25, 10, 10x6.3 and then 10.5. Hope that makes it clearer and I certainly appreciate all the assistance. Maybe what I am trying to do is in fact impossible.

[librebel]

Hello @spider,

This is actually more tricky than one might have expected in the first place…

The values in your column aren’t quite numbers, but arithmatical expressions whose datatype = string.
Furthermore these arithmatical expressions contain a non-standard operator for multiplication ( “x” instead of “*” ).

In order to sort your column as numbers, it is necessary to Evaluate these arithmatical expressions first.
Unfortunately Calc has no built-in EVAL function, but you can define your own Eval() function in Basic, which can then be called from a Calc cell formula.
There exists already a powerful eval() function in Python, that could be used for your purpose.

So my suggestion for proceeding in this case would be as follows:

1. use the Python eval() function for the evaluation of your string expressions,
2. write a Basic function Eval() to invoke the Python function from step 1,
3. create a new column B and set its cell formulas to =EVAL(A1) , =EVAL(A2) , etc,
4. then sort column B, with selection extended to column A in the sort.

---

Example Python function:

def evalmath( expression ):
	try:
		return eval( expression,{ "__builtins__" : None }, {} )
	except:
		return expression

---

Example Basic function:

Function eval( strExpression As String )
REM This Function invokes a Python function to Evaluate the specified expression, and returns the result.
REM The Python function is named "evalmath" and is stored inside a file called "test.py" inside the LibreOffice User Scripts Folder for Python.
REM ( In Ubuntu this folder is located at: "/home/username/.config/libreoffice/4/user/Scripts/python" ).
REM If your own Python evaluation function is named differently, or is stored in another .py file,
REM then you should alter either or both of the two constants below:

	Const cPyFunction As String = "evalmath"	REM Put here the name of your Python evaluation function.
	Const cPyModule As String = "test.py"		REM Put here the file in which the above function is stored.

REM Inside the file "test.py" there should be these 7 lines ( without the apostrophes ' at the start ):

'#!/usr/bin/env python3
'def evalmath( expression ):
'	try:
'		return eval( expression,{ "__builtins__" : None }, {} )
'	except:
'		return expression
'g_exportedScripts = evalmath,

	REM First we got to change all the occurrences of "x" and "X" into "*".
	Dim strNormalExpression As String : strNormalExpression = strExpression
	strNormalExpression = Join( Split( strNormalExpression, "x" ), "*" )
	strNormalExpression = Join( Split( strNormalExpression, "X" ), "*" )
	strNormalExpression = Join( Split( strNormalExpression, "-" ), "-" )

	REM Now we can call the Python evaluation function via BASIC as follows:
	Dim oScriptProvider As Object, oScript As Object
	Dim aParams(0), aOutParamIndex(), aOutParam()
	oScriptProvider = ThisComponent.getScriptProvider()
	oScript = oScriptProvider.getScript( "vnd.sun.star.script:" & cPyModule & "$" & cPyFunction & "?language=Python&location=user" )
	aParams(0) = strNormalExpression
	eval = oScript.invoke( aParams(), aOutParamIndex(), aOutParam() )
End Function

Using the above functions i tested your sample sequence and it came out as:

2.5-3 1,00 2,00 3,00 5.00 6.00 7.00 7.00 7.00 7.00 9,00 10,00 5x3 5x4 5x4 6X4 6x4 6.5x5 10x7 10x7 10x7 10x7 10x7 10x7 10x7 10x7 10x7 10x7 12x8 10.2x4.2x2.9 6.03x5.42x4.14 14x10 14x10 18x13 10.5x10x6.3 12.0x9.5x6.6 11.5x10.8x6.8 11.5x10.8x7.6

[Grantler]

You need all the cell values as text in column B: =TEXT(A1;"@")

Then “extract” the first two “letters” in column C: =LEFT(B1;2) (it only works satisfyingly with 2 letters; 3 didn’t gain good results…);

Combined: =LEFT(TEXT(A1;"@");2)

(For refining use =MID(B1;3;1) or more single “values” of the given string. In this case you should have an additional help column. Probably anyone knows to combine in one larger formula?)

You need at least one help column, anyway.

Then sort the natural way. For more than one help columns chose each, beginning with help column of first two letters…

Result (only first two letters):

1 | 2 | 2. | 3 | 5. | 5x | 5x | 5x | 6. | 6. | 6. | 6x | 6X | 7. | 7. | 7. | 7. | 9 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 11 | 11 | 12 | 12 | 14 | 14 | 18 |