Your Subject says "Lookup Function", but the solution below assumes that
you're not actually asking about the Excel LOOKUP Function. Rather, the
formulas below extract the city, state, & zip from a single cell (A1), &
return those 3 fields in 3 separate cells (A2, A3, A4).
A1: New York, NY 10001
A2: =LEFT(A1,FIND(",",A1)-1)
A3: =MID(A1,FIND(",",A1)+2,2)
A4: =VALUE(RIGHT(A1,5))
You'll probably need to format A4 using the Zip Code format:
Format Menu | Format Cells | Number Tab | Special | Zip Code
This prevents zip codes such as "00101" be displayed as "101".
If you don't want to convert the zip code to a value, then use this formula
in A4:
=RIGHT(A1,5)
With this formula, the zip code will be text, & Zip Code formatting isn't
needed.
You probably know this, but just in case - you may want to place the
formulas in B1, C1, & D1.
These formulas are relatively simple, and may not work if you have +4 zip
codes, extra spaces, missing spaces, or other syntax variations. The
formulas assume your data has this consistent syntax:
"New York, NY 10001"
Good luck.
