enter this in C1027, then copy to your range
=LEFT(D1027;FIND(" ";D1027;1))
and this in E1027, copied to your range:
=IFERROR(RIGHT(D1027;LEN(D1027)-FIND("#";D1027;1));"")
oyu might need to change the ; separator in formulas to your regional settings ,
There are differencies in your data, the formulas or a macro is dificult to adjust to all your types of entries in D column