macro formula to clean data in large file

I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. I realize this is probably better served with VBA but I do not have those skills. If anyone has thoughts using macros or formulas, I would appreciate it. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.

Thank you for your consideration. Brady