Most Effective method to group records by letter?

[Claje]

Hi,

I have a field with letters A-Z in it.  I want to split my data into four "buckets"

A-F
G-L
M-R

S-Z

I recognize that I can call out every single option in a SWITCH statement, or write a REGEX_MATCH for each of these, but is there a smarter way to handle a problem like this?

The particular function that I can't seem to reliably create is some sort of "Position in Alphabet".  Is this something that exists which I just haven't found?

[Bob_Blackey · Accepted solution]

Here's one way - 

You could use the formula tool and the formula (with the example column "letter")

CharToInt([Letter])-64

which give you the position.

You could then use the Tile tool to set up the groupings.

[Claje]

Ah - I think what I was missing is the need to normalize the data.

I tried that and was getting inconsistent results, not thinking of the fact that A/a would naturally have different integer values.

Thanks!

[Bob_Blackey]

Glad to help - you can use the Uppercase() function to normalize to uppercase.

Cheers,

Bob