Hi and welcome! There are certainly roles that your specialization and interests would fit in, although in terms of a professional career, openings may be less numerous and limited to larger studios that use lots of specialized materials in their games, (Bungie and 343 come to mind). A broader position would be a Tech Artist, but there are a lot of things that can fall under that job description outside of materials.
Substance is a great tool to know, and although it’s slow going, vfx is moving to using it more because of its auto tiling and non-destructive workflow.
Like Cel mentioned, the most important piece of software to learn is just an engine. UE4 and the latest version of Unity both have node-based material editors, which fundamentally work just like Substance. There are tons of awesome effects you can make that are entirely material-based too. I’d check out this thread Tech Art Fundamentals tutorial series
For some resources on learning Unreal Materials (these same concepts can apply to Unity as well) I’d check out these:
Paid: https://www.udemy.com/3dmotive-advanced-vfx-and-cinematics-for-games-with-udk/
http://www.cgriver.com/udk/7546-advanced-vfx-masterclass-bundle.html
Trinity3D - 3D Animation Software Source
Free: https://www.youtube.com/playlist?list=PLoAbgsIP97amyu8vLy-r6kuYwzHtHB6Lk