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

jmac1947. Thanks for the quick reply. This is a nasty data set. I was actually just going to split all the characters together in the same cell separated by spaces in separate columns and do some sorting to figure it out. I have added a large sample for your review. The issue with the address data in each cell is that it is all over the place. I will use this to find addresses in the two separate columns that do not match. The issue is that some may read "101 main street, anywhere, USA" and the compared cell next to it may read "%debbie and jack trust 101 main ST (abbr for street), anywhere, USA".

Also, one cell may have breaks in it to format the address correctly and the other may not. "Typically" US addresses are formatted '101 Main ST, Dallas, TX'. I removed the commas onthis sample as they were also inconsistent. So, I have been crash coursing VBA today and still beating my head against the wall trying to break all this data in the cells into separate cells and then I can do manual work to filter out matches or near matches. BTW, I have many of these files from different entities and they are all different but have the same issues. I need a data loader that I can map the cells to and load it into a database but that is above my ability. Any suggestions would be greatly appreciated.

In the end, I need to be able to match the data in the opposing address cells beside each other and see if there are matches in the nasty added data.

Thank you again, I plan to be her often as I am totally amazed at what can be done with VBA and I have to learn it now. Thank you, B

Hi Brady,

You are not kidding when you say "nasty", this is serious hard work in spades.

OK, I cannot promise anything but I will give it some tries. As a first step I will try to get a "generic" split of the data into individual cells, that shouldn't be tooooo bad (famous last words I can hear my self saying already )'

This could end up being the death of both of us but it may also be a bit of fun along the way (I feel better already knowing I am going to have fun )

John (aka jmac1947)