Thanks!
Here's a sample of some of the worst: (I've replace the real data with nonsense)
77 old country rd new york ny 10001 H 9876542581 father dan 9876543210
9876 dans deals way ddf ny 115812 WC (111) 598-3254 - here the zip is intentionally too long bec that's how i have it
123 anywhere pl cleavland oh 11225 HC 1234567890 W 1234567891
As you can see there really is no pattern, some numbers have the (), some don't.
So this might be a start:
Put your data into column A of a google spreadsheet
Put this into column B and copy it down:
=regexextract(A1,"(?i)([\d\w ]* (rd|way|pl)) ([\w ]*) (AL|AK|AZ|AR|CA|CO|CT|DE|FL|GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|TX|UT|VT|VA|WA|WV|WI|WY) (\d*) ([\w ]*) ([\d --()]*) (\D*) ([\d --()]*)")
The problem is that you'll have to manually scan through all the rows to make sure nothing is missing, once you do that, it might just pay to do it manually.
But maybe give it a try, see if any rows return errors or are missing data, and we can tweak it as needed.
Also - this section: (rd|way|pl) will need to be modified to include all the options that your data includes for road/street/drive etc. It's not case sensitive at least