Substitute formula lower and upper case character and long text problem

Hello Forum Leaders,

I have text in single column such as Text,Text with Numerical,semi colon,back slash and lower and upper case character.


In Column A2 as a long text. The Formula considered the Text two character only.

Another one problem The Formulas couldn’t considered some Texts (XXXXX)and Lower and Upper Case character Problems(XXX-xxx).

File attached for your reference.

Please help me.