Can't create a formula, referring to a reference Column?

A MID formula on a reference column returns a TypeError.
I duplicated the reference column and the MID function works.
Surely, I don’t have to duplicate every reference column to do formulas. What am I doing wrong?

In Reference Columns, the column value always references the entire record in the underlying table.

The displayed value can be any column from that record, as selected in Show Column, but that shown value is not the value in the cell.

What I would do is create a column with the formula $Variant_SKU.SKU that simply returns the SKU being referenced in that row, as a value that the formula can work with. I mocked up an example here:

If you’re unfamiliar with this formula, you may learn more about it here: Reference columns - Grist Help Center

The last column could be hidden. Now when selecting an SKU in the second column, the hidden column would update, and the formula would work as expected.