# 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! Commented Oct 25, 2020 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. Commented Oct 26, 2020 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))) Commented Oct 26, 2020 at 19:00
• @ian Brilliant! Commented Oct 27, 2020 at 10:15
• @Alasdair then accept the answer with the checkbox
– Bera
Commented Oct 28, 2020 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 0s 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.