Using Regex to Determine Flight Types

Many Virtual Airlines use logical callsign systems where you can determine the flight type (Scheduled, Charter, Repositioning, Training) based on the callsign pattern. This guide shows how to use Regular Expressions to automate this categorization.

Regular Expression Basics

Regular Expressions (regex) are patterns used to match character combinations in strings. You define character types and quantities.

Let's analyze the callsign TVS3BD (a Smartwings scheduled flight using ARCID format):

The pattern is: Letter-Letter-Letter-Number-Letter-Letter

As a regex: [A-Z]{3}\d{1}[A-Z]{2}

Breaking It Down

• [A-Z]{3} = 3 letters from A to Z = "TVS"

• \d{1} = 1 digit (0-9) = "3"

• [A-Z]{2} = 2 letters from A to Z = "BD"

Matching Multiple Patterns

What about TVS56H? This looks like a different pattern (3 letters, 2 numbers, 1 letter), but you can match both with one regex using ranges:

[A-Z]{3}\d{1,2}[A-Z]{1,2}

• \d{1,2} = 1 or 2 digits

• [A-Z]{1,2} = 1 or 2 letters

Practical Flight Type Patterns

Ferry/Positioning flights (e.g., TVS240P, TVS240F):

[A-Z]{3}\d{3}[FP]{1}

Note: [FP] matches only F or P (specific letters in brackets).

Charter flights (e.g., TVS2240 - 4 digits):

[A-Z]{3}\d{4}

Using Regex in Google Sheets

Google Sheets can use regex in conditional formulas. Basic syntax:

=IF(REGEXMATCH(A1;"[A-Z]{3}\d{4}");"Charter Flight")

For multiple conditions, use IFS instead of IF:

=IFS(REGEXMATCH(F2;"[A-Z]{3}[0-9]{3}[FP]");"Repositioning";REGEXMATCH(F2;"[A-Z]{3}[0-9]{4}");"Charter";REGEXMATCH(F2;"[A-Z]{3}\d{1,2}[A-Z]{1,2}");"Scheduled";TRUE;"")

Deriving Other Fields

Once you have a Tag column with flight types, derive other import fields:

Type field:

=IFS(T2="Scheduled Flight";"scheduled";OR(T2="Charter Flight";T2="Business Jet Flight");"charter";T2="Repositioning Flight";"repositioning";TRUE;"")

Flighttype field:

=IFS(T2="Scheduled Flight";"s";OR(T2="Charter Flight";T2="Repositioning Flight");"n";TRUE;"")