SMART Formule Like Vlookup Required Like AI

[kingg9999]

Dear All,

Please Help

I receive daily 1000row of data with address merge together i have to search one by one to find particular word to the accurate address for the system to understand and the address which client gives is not accurate some merge,some with comma some without spaces.

Is there any Smart formula as some things i find in Zone name ...Some i find in Area name one one formula or multiple formula

Result is shown as well and below is the google link.

Loading Google Sheets

 

[kingg9999]

filling cell with a specific text if another cell contains any of the specific keywords​

Similar to this but the Formula is not working for me gives 0 result

=IFERROR(LOOKUP(2,1/SEARCH(H$2:H28$,n2),I$2:I$28),"NA")

 

[kingg9999]

anyone ?