There are two ways to split text fields (like State names) into alphabetical groups.
- The first uses ASCII codes, where letters are converted into numbers. This works but is more complicated and harder to read.
- The second uses simple alphabetical comparison (e.g. checking if a letter is before or after ‘N’). This is much easier to understand and maintain.
It’s good to know both, but in most cases the alphabetical method is the better option.
1. Using ASCII codes.
What is ASCII? ASCII (American Standard Code for Information Interchange) is a system that assigns a numeric value to every character. We can use if statements here to section off values based on their number equivalent.
For example:
- A = 65
- M = 77
- N = 78
- Z = 90
Lowercase letters have different values:
- a = 97
- m = 109
- n = 110
- z = 122
In our data, our values all start with a capital letter, so we can use the following syntax to extract the two groups.
How it works:
- LEFT([Field], 1) extracts the first letter
- ASCII() converts that letter into its numeric ASCII code
- In ASCII, A = 65 and M = 77
- So anything ≤ 77 falls in A–M, and the rest in N–Z
The same idea can be applied to lowercase letters if that is how your data looks using <= 109.
2. Alphabetical Comparison Method
Instead of converting letters to numbers, you can compare characters directly:
How it Works
- LEFT([State],1) → gets the first letter
- UPPER() → standardises it to uppercase (so it works for mixed data)
- 'N' → acts as the split point
In alphabetical order the letters A–M come before 'N' and the letters N–Z come at or after 'N'. So anything less than 'N' falls into A–M, and the rest into N–Z.
This is better than the ASCII logic as it is more readabe and handles mixed cases automatically with UPPER().