If you expand the list to see the columns available for join, right at the bottom there is an option to "Create Join Calculation". You can copy your formula and include it in this and use in the join as required.
Ok, this does seem to work. I can write the substring expression into the join calculation itself to achieve the desired result. Thanks for this solution. I'll still maintain that it would be a lot easier to be able to reference the column itself. That's how other reporting tools typically work in my experience.
I understand your concern. You could create an idea if it's not already there and get people to vote on it so that Tableau may act on it. But, for now, it's better to have a solution than no solution at all.
Anyway, please mark this thread as answered so that it gets closed.