Skip to main content

I’m trying to create a system that generates simple codes from employees’ SSNs for internal use. The SSN is stored as numbers, e.g., 123-45-6789.

I want to create a new formula field that converts each number into a letter using a reverse alphabet mapping:
Z = 1, Y = 2, X = 3 … A = 26

For example:

  • 1 → Z

  • 2 → Y

  • 0 → J (or another letter of choice)

Is there a formula in Airtable that can do this conversion directly?

For reference, numberstoletters.com does similar conversions, but I want to implement it directly in Airtable.

Yeah there are a couple different ways to handle this. The easiest (since I’m not a Regex expert) is using a nested Substitution formula. Try this…

SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
        ssn,
        '2','b'),
        '1', 'c'),
        '3', 'd'),
        '4', 'e'),
        '5', 'f'),
        '6', 'g'),
        '7', 'h'),
        '8', 'i'),
        '9', 'j'),
        '0', 'k')

Replacing the ssn field with whatever your field is titled. You can also pick the letter each number is meant to represent. The only thing here is it handles each digit individually, so 26 is “bg” not “a”. If you want to replace across the whole alphabet then I think you’d need a Regex formula that someone else might be able to drop in.

Airtable doesn’t have very robust formulas. You could probably use a REGEX formula to do it, but outside of that, you would need to nest multiple SUBSTITUTE() functions like this:

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Your Social Security Number Field},
"1", "Z"
),
"2", "Y"
),
"3", "X"
),
"4", "W"
),
"5", "V"
),
"6", "U"
),
"7", "T"
),
"8", "S"
),
"9", "R"
),
"0", "Q"
)

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

I did not see that ​@DisraeliGears01 had already responded before I typed up my response!

Great minds think alike! :)

- ScottWorld, Expert Airtable Consultant

Terms & ConditionsAccessibility statement