I have a table where I pulled the numerical part from a text field and used Number() to convert. When I go to Sort and specify Smallest To Largest, it does not work as expected. Seems like a silly issue, but unsure if there is some hiccup I am overlooking with converting text to a number.

What it does β 10, 11, 12, 21, 31, 4, 5, 6
What is expected β 4, 5, 6, 10, 11, 12, 21, 31

Cathy
Category updated
October 25, 2023, 2:38am
2

Hey iron-brian, sorting on a Number column should definitely yield the numeric order.

To help us to be able to accurately reproduce it, could you please let us know:

the formula you used to pull the numerical part from a text field
some examples of the values in that text field
what cloud data warehouse your data is in as different warehouses may have different behaviors when casting types
Youβre also always welcome to pop open a live chat for direct support from within the Sigma app:

1 Like

Hi @garethbrickman , I hope the below details help out.

```
Number(RegexpExtract([ORIGINAL STRING], "[A-Z]*[-][a-zA-z]?([0-9]*)", 1))
```

Data is in Snowflake. Though I can find more details if helpful.

Here is an example of the source for the numerical value:

Original String
Resulting Number and Resulting Sort
LM-D1-E-01
1
LM-D10-E-01
10
LM-D11-E-01
11
LM-D12-E-01
12
LM-D2-E-01
2
LM-D3-E-01
3
LM-D4-E-01
4
LM-D5-E-01
5
LM-D6-E-01
6
LM-D7-E-01
7
LM-D8-E-01
8
LM-D9-E-01
9
LM-E1-E-01
1
LM-E10-E-01
10
LM-E11-E-01
11
LM-E12-E-01
12

Here is my working reproduction, also on Snowflake:

Details of the sort applied:

Thanks @garethbrickman ! I finally realized my mistake. I was not landing the value in the grouping I was expecting to end up sorted