» Splitting a Full Address into Three Separate Cells
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Separating the addresses shown in column A into three columns, one each for City, State, and Zip Code.
Solution:
Use the LEFT, MID, RIGHT, and FIND functions as shown in the following formulas:
To extract the City: =LEFT(A2,FIND(",",A2)-1)
To extract the State: =MID(A2,FIND(",",A2)+2,2)
To extract the Zip code: =RIGHT(A2,5)
Book Store:
Recommended Books:
- Yes, You Can Time the Market!
- Microsoft Excel 2002 Simply Visual
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
- Investing for Dummies, Third Edition
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
gmoney10111
How do I use this?
Had to change "," for ;
=LEFT(A2;FIND(";";A2)-1)
I use Microsoft Excel 2003