Converting an address in one cell into multiple cells

Hello everyone!

If anyone can help me out with this I'll mail you a beer!

I have a list of addresses in this format: Firstname Lastname 123 Paved Rd City, ST 12345-6789
And since every address is different, i cant use the spaces to set the beginning and end of data to extract, example : 123 Stone Paved Dr Rock City, 123 Red Stone Paved Blvd Town City Center, etc:

I need a formula to convert them all into separate cells as follows: [Firstname Lastname] [123 Paved Rd] [City] [ST] [12345]

I have a formula for the name, but making one for the address that will work is giving me trouble. I need a formula that sets the second blank after the Lastname as the beginning for the address and because street names vary, it needs to look for " Rd " or " St " or " Ave " or " Blvd " etc for the end position...is this possible?
And can that address be used to distinguish where the city name begins?

Any help would be much appreciated. I'm rather new to excel but I'm talented at Photoshop and would gladly do some work for anyone that can help. I have attached what I have so far, with three half finished versions of my attempts.

Thanks,
Kenny

It would help us if you can tell us what elements of the string will be constant, and which are variable

For example - It will always end in 10 char zip code
prior to zip code will always be 2 char state code
city - is variable, either one or two words (separated with a " ")
address - is variable, either one, two or three words (separated with a " ")
name - is always two words, never one or more than three

Originally Posted by ThirtyTwo

It would help us if you can tell us what elements of the string will be constant, and which are variable

For example - It will always end in 10 char zip code
prior to zip code will always be 2 char state code
city - is variable, either one or two words (separated with a " ")
address - is variable, either one, two or three words (separated with a " ")
name - is always two words, never one or more than three

Exactly the way you described it Thirty Two, thanks for your reply. Firstname lastname and spacing is constant, address is variable, but will always end in " Ave " or " Dr " or " Blvd " etc...city name is variable, but will always follow the end of the address and will be followed with a "," and the state and zip are constant format. Zip will need to be reduced to the first 12345 before the "-" and after the " " following city.

The bolded letters are variable in name length and the colored ones will match a short list for illustration

Firstname Lastname 123 Paved Rd City, ST 12345-6789

Richard Buttrey, you are a god among mortals. Thank you so much!
Now a stupid question: I tried to add some new street types to K (Ln, Ct, etc) and the address portion is coming up #VALUE! for those addresses. Do I need to modify anything in the helper columns?

So for the most part, these formulas work. But I have run into some issues where they don't and I cant for the life of me determine why not...
attached is a list of addresses and within them are the ones that appear as if they should be working(but are returning #Value!

Thanks again!