To extract texts from a cell

Dear friends,

I want to extract texts from a series of data. However I couldn't use Left(), Mid() or Right() due to these data are not in standard format. Is there a more efficient way of doing it other than extracting them manually?

please see attached.

Grateful if anyone can help.

Regards