Splitting data in a cell to multiple

[steelstorm]

Hello,

I currently receive a spreadsheet that has the majority of the address listed in a single cell. I am having issues wrapping my head around how I can have this broke into multiple cells like my format needed below.

I realize this would have to be able to recognize different types of roads like st, alley, drive....but I have not been fortunate enough to find any type of examples like this online for any help.


How the data is received:
722 SAINT OUEN ST

How I need the address broke down:
Street NO: Name: Type:
722 Saint Ouen St


Any help or feedback is greatly appreciated, this one is definitely out of my league.

[Bob Phillips]

If there is some rule that can be applied to all rules, then it can be coded. If not, say the number might be 22 Block 4, or the town might have one, two, three words, and so on, it will be very difficult to parse.

[GTO]

Only to add, and hopefully sensibly, to what xld has commented: I read your post several hours ago and wanted to respond, but was called away. My comment would be that if the logic escapes you as to a pattern that is dependable, you may wish to provide a sample workbook with representative data. As Bob (xld) has already said, we need some dependable rule that can dependably make sense of the input data. It might be as simple as commas, but all too often, addresses can become an awfully hard thing to get a good split on.

More oft however, it has seemed to me (totally subjective), that the poster spoon feeds what he or she is up against, raw data wise.

I realize that creating truly representative data is time consuming; but that is what I would suggest (if the amount of data is worth the investing of time in attempting a dependable return)

Mark