at the moment I am trying to create an indexed vector grid via "vector -> research tools -> vector grid...", in the following format:

A1 A2 A3 ...
B1 B2 B3 ...
C1 C2 C3 ...
.. .. ..

The creation of the grid is no Problem, but I am stuck with labeling the individual polygons.

I already wrote an Excel Visual Basic Project, which creates a script for the field calculator and helps me to label each row from A to X. Now I want to label the columns with numbers from the first one to the last. Do you have any idea how I could use the @row_number to specify the column?

Here is the output of the script for the field calculator:

WHEN ID>-1 AND ID<5 THEN concat('A',@row_number)
WHEN ID>4 AND ID<10 THEN concat('B',@row_number)
WHEN ID>9 AND ID<15 THEN concat('C',@row_number)
WHEN ID>14 AND ID<20 THEN concat('D',@row_number)
WHEN ID>19 AND ID<25 THEN concat('E',@row_number)

2 Answers 2


You have two possibilities...

Existing plugin

there's already a plugin to do this called Create Indexed Vector Grid (which I wrote, mainly so I could do road atlas-style maps of cities).

Once installed it appears under Vector > Create Indexed Grid menu. It'll add the labels as fields to each cell for you (and optionally, add "headers" for your rows and columns).

It's experimental, so if you can't see it in plugins manager you'll need to enable the "show experimental plugins". There's a link to a blog post explaining how to use it in the plugin description in Plugin Manager.

With inbuilt create vector grid

That might not be suitable, though - the plugin only works on the canvas extent or layer extent, it doesn't let you set the extent manually.

Use the modulo operator to get the column number, and simple integer division to get the row number. That way you only need one expression, and don't need to write code to generate a script.

I created a grid using Create Vector Grid and assigned a new field with @row_number (called this field idd). As you can see it's 15 cells wide wide, and cells are numbered as follows...

enter image description here

Now, assuming you want 1-indexed (so you assigned a value of 1 to first cell)

  • row number is to_int(floor(to_int(idd-1)/15))
  • column number is (idd-1)%15, which we use to convert to a character

the following single expression should work

char(65+(to_int(floor(to_int(idd-1)/15)))) || substr('0123456789ABCDEF', ((idd-1)%15)+2,1) 

that turned out to be more complex than I thought, largely because the divide is a floating point operation, rather than an integer division (like // in python)


enter image description here

Note that the definition of substr changed in QGIS 2.14 so this will need to be tweaked for older versions of QGIS.

  • Very impressive!
    – mgri
    Feb 14, 2017 at 23:33
  • 1
    Thank you! The problem is, that I can not download all PlugIns in the network I am working in. That's why I chose the solution with the script (and because I am not familiar with python). Maybe I got another solution, which I will post later. Feb 15, 2017 at 9:34

Based on my approach I found another solution, which I want to contribute. So far I used the field calculator to create a new field for the index. As I realized I might need to do this task more than one time, I used Visual Basic to write the function based on the count of columns and fields.

Excel Sheet

The mathematic function for the column number is a simple linear function.

ID = column + sum of columns * row

Column = ID - sum of columns * row

The indexed vector grid will look like this (it's a cropped image, the index starts with A1):

enter image description here

Here you can find the code for excel:

Private Sub CommandButton1_Click()

Dim Feld As Integer
Feld = 0
Dim code As String
code = ""
Dim Zeile As Integer
Zeile = 0
Dim Bez As String
Bez = ""
Dim Bez2 As String
Bez2 = ""
Dim B1 As Integer
B1 = 0
Dim B2 As Integer
B2 = 0
Dim B3 As Integer
B3 = 0
Dim BezEnd As String
Dim SA As Integer
SA = 0
Dim ZI As Integer
ZI = 0

TextBox1.Value = ""

For Zeile = 1 To ActiveSheet.Cells(3, 2) 'write first part: CASE, WHEN, ELSE
    code = code + "CASE" & vbNewLine & "WHEN ID>" & Feld - 1 & " AND ID<" & Feld + Tabelle1.Cells(2, 2) & " THEN concat('"
    Feld = Feld + ActiveSheet.Cells(2, 2)
    If B1 = 26 Then 'count the alphabet from A to ZZZ
        B1 = 0
        B2 = B2 + 1
        Bez = Bez2 & Chr(64 + B2)
        If B2 = 26 Then
            B2 = 0
            B3 = B3 + 1
            Bez2 = Chr(64 + B3)
        End If
    End If
    B1 = B1 + 1
    BezEnd = Bez & Chr(64 + B1)
    'define column
    SA = ActiveSheet.Cells(2, 2)
    code = code + BezEnd & "',@row_number-" & SA & Chr(42) & ZI & ")"
    ZI = ZI + 1
    If Zeile <> ActiveSheet.Cells(3, 2) Then
        code = code & vbNewLine & "ELSE" & vbNewLine
    End If

Zeile = 1 'reset row

For Zeile = 1 To ActiveSheet.Cells(3, 2) 'END
    code = code & vbNewLine & "END"

TextBox1.Text = code 'write into TextBox
End Sub
  • how do you use the code in excel. do I need to input the spalten(columns), zeilen (rows) and felder (fields) into the excel sheet in the same cells? is fields just row*columns? When I try pressing play on your code I get a run time error. it highlights TextBox1.Value = "" when i run debug on it. do I need to input anything there?
    – B-C B.
    Jul 8, 2020 at 17:50
  • @B-C B. The number of columns and rows have to be in the same cells as specified in the VBA-Code (columns: B2, rows: B3). Fields is kind of redundant. I used it as a workaround to calculate the number of rows (fields/columns=rows). By this I did not have to count the number of rows and columns in QGIS. Jul 10, 2020 at 8:26
  • @B-CB. As for the run time error: TextBox1.Value = "" is used to clear the Textbox. You could try TextBox1.Value = Null instead. Jul 10, 2020 at 8:32
  • To make things easier, I uploaded my Excel file to a github repository. Maybe you would like to have a look at it. I hope it is okay, to post this link here: github.com/ChrisPBacon33/GIS-Tools Jul 10, 2020 at 9:53
  • I ended up using @G.monticolo answer here gis.stackexchange.com/questions/330760/…
    – B-C B.
    Jul 16, 2020 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.