# Sorting text column by numbers with letters

I have a text column. It's made up of the numbers 1, 2, 3 up to 1650, which are currently not in increasing order. However, it also includes text such as 520a, 639a, 639b, 639c. Sorting on increasing value would produce 1, 10, 100, 1000, etc, rather than 1, 2, 3, ...

I know how to convert from text to integer, which would work if my cells did not include alpha characters.

How can I sort to end up with 1, 2, 3, ..., 10, ..., 100, ..., 519, 520a, 521, ..., 638, 639a, 639b, 639c, 640, ..., 1650?

you could try creating a new decimal column in field calculator with a precision of 2 and use the following if statement in the expression window:

``````if (upper("yourTextField")= lower("yourTextField"),"yourTextField",
regexp_substr("yourTextField",'(\\d+)')+(strpos( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
upper(right("yourTextField",1)))/100))
``````

It tests to see if there is a letter in your text field - if not then use the original value, if so it uses regexp_substr to extract the number part and then adds the strpos value divided by 100 to make the value added less than one. The following image shows an example of the outputs.

• Many thanks for the two replies. I tried Kazuhito's answer first, but the field calculator said there was a problem with the function (despite copy and paste) Then I tried Ian's, and it worked. Problem solved! – Alasdair Oct 25 '20 at 17:50
• This is really the most elegant and simple solution, using nothing but QGIS functions. I'll have to shelve this is the back of my mind for when I need it. – Gabriel C. Oct 26 '20 at 12:32
• You can also do this directly by sneaking to @JGHs answer: right click the `yourTextField`-column in the attribute table, click sort and add `to_real()` to this expression: `to_real(if (upper("yourTextField")= lower("yourTextField"),"yourTextField", regexp_substr("yourTextField",'(\\d+)')+(strpos( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', upper(right("yourTextField",1)))/100)))` – MrXsquared Oct 26 '20 at 19:00
• @ian Brilliant! – Kazuhito Oct 27 '20 at 10:15
• @Alasdair then accept the answer with the checkbox – BERA Oct 28 '20 at 18:23

I recommend Sort and Number plugin, which can take multiple fields as sorting criteria.

(1) Preparation

To allow the plugin to recognize the number part and the character part of your `textcolumn`, create two separate columns by expressions in the Field Calculator.

``````num_part (integer): regexp_substr("textcolumn", '(\\d+)')

char_part (text): regexp_substr("textcolumn", '([a-z]\$)')
``````

(2) Run Sort and Number plugin. It will create a new `order` field.

(3) Sort by the `order` field. You will see something like below.

Extending on @Kazuhito preparation step, you can achieve such sort directly.

Open the attribute table, right click on a field column name, choose `sort`

Then enter the following code, replacing the column name by your own. Change the `10` with the max number of digits / letters.

Basically, this code extracts the number, adds `0`s before so every number is exactly 10 digits, then it does the same with the text part, adding `_` so each text is exactly 10 characters long. Then is aggregates both together using `||`. Since every entry is `0` and `_` left padded, a regular text sorting can now be applied.

`````` lpad( regexp_substr("id", '(\\d+)'),10,'0')
||