Figart Consulting’s Excel Nifty Tip #1
Figart Consulting will be offering a free series of Nifty Tips to help you solve common office problems without strangling your co-workers, clients or supervisors. Since so many of these issues involve Excel, the series will start there. If a solution is specific to a software version, we will make sure to make that clear in the post. If not, you can pretty much assume that this will work on any version released since ‘97.
NIFTY TIP #1: Splittng a full Address into Three Separate Cells*
Problem: You have inherited a mailing list that includes the City, State and ZIP all in the same column. Now, while the first thing you want to do is murder the person who set the spreadsheet up this way, don’t panic. Killing them is only a momentary pleasure and is bound to get you talked about.
You can solve the problem so that you can use imports, export and Mail Merges, more easily without retyping the darn thing. What you need to do is to split the City, State and ZIP into three separate columns.
Solution: Use the LEFT, MID, RIGHT and FIND functions as shown in the following formulae:
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)
*Special Thanks to Peter J. Vinton, Jr., originator of the Excel Nifty Tips series.
Related posts:
- Figart Consulting’s Excel Nifty Tip #1.5
- Nifty Tip #3: CONCATENATE
- NIFTY TIP #5: Adding or Subtracting Amounts from Different Ranges
- Nifty Tip #4: Organizing the Data Table for Proper Use of the VLOOKUP Formula
- Nifty Tip #2: Combining the VLOOKUP and MATCH formulas
« Figart Consulting’s Excel Nifty Tip #1.5 | Home | Social Networking Over the Decades »
Comments
Nice tip! If you’re interested in more Excel productivity resources head to http://www.facebook.com/office
Cheers,
Andy
MSFT Office Outreach
Leave a Comment