You might be able to use a formula similar to the following to accomplish what you need:
LSplit(LSplit(UPPER(Addr),2,"APT ",2),2," ",1),
LSplit(LSplit(UPPER(Addr),2,"APARTMENT ",2),2," ",1),
LSplit(LSplit(UPPER(Addr),2,"STE ",2),2," ",1),
LSplit(LSplit(UPPER(Addr),2,"SUITE ",2),2," ",1),
LSplit(LSplit(UPPER(Addr),2,"UNIT ",2),2," ",1),
It converts the address field to uppercase, just in case any lower case character sneak into the field, as InStr is case sensitive. To add other test strings is pretty straightforward.
It would be nice to use something like StrIn(Addr,"APT","STE","UNIT") as a custom function but that would need some kind of looping or recursiveness, I believe, which Monarch does not support.
Does anyone have another solution?
I see Keyser has spotted the invisible data - and included trailing spaces - so RAPTOR AVE won't be parsed as containing "APT ". But address data is dirty stuff - and if you have customers in "Apt Road, Ibhayi, Port Elizabeth, Eastern Cape, South Africa" (trust me, it's on Google Maps, and worth the detour just to see that it runs parallel to APS road and APU road...) then you might lose them. Less exotically, there's a Unit Street in Providence, Rhode Island and another in Los Angeles.
You can amend the If() to exclude these cases - but tread carefully. I wouldn't like to have to build the expression that excluded STE meaning SUITE from STE meaning SAINTE. In the UK, I think you can buy, or subscribe to, a database from the Royal Mail with the complete address data by postcode, so it should be possible to link to this and score your addresses on matches by number, house name, street name, town etc. and then pass out the remainder for manual checking.
But this is something that CRM systems and good master data management should have addressed (pardon the pun) long before the data ends up in a report for Monarch...
Just to reinforce Olly's observation about Address data quality ....
If you get about 80% accuracy you are dealing with a fantastically good data set - especially if the source is known to be via manual input rather than some sort of high quality well verified address checking database.
My advice would be to get what you can using the Address Block functionallity and some calculated fields but make heavy use of the 'Error' field flag in the Address Block feature and then monitor the results to refine where possible. Once you hit about 90% you will have got about as far as you can take it on a 'worthwhile return for the effort' basis.
The good thing is that once you have your methods working they can be used on future models quite easily.
If you are dealing with address formats from multiple countries ... the task should keep you busy for some time.
Until recently we used a product from Experian, QAS (Quick Address Solution). It did a very good job at separating data. It was used to scrub the address after Monarch had processed the file. It has since been replaced by a return file from our letter vendor.
As Grant stated, if you're getting 80% accuracy, you're doing good. Unless the address is in a postal block, the Address Block function does not always return the best results. The error flag will help, but it depends on the data.