splitting up a street address / city

[kin1]

can someone tell me if there's a function or a way to extract city from an address string?

ex.. 1234 main school rd new york

is there a way to take "new york" and putting it into it's own column? also there can be times where the city is only 1 word .

thanks!

 

[Excelestial]

There will need to be a very definitive pattern that tells the formula where to end begin or end the extract. For instance, will every street address end in rd, or st, or ave, or blvd, etc...

If so, then it is possible. If there is nothing to distinguish the street from the city, it will take some manual manipulation....painfully...record by record.