Home
Tips & Tricks
Excel Functions
Excel Formulas
Applied Formulas
Applied Functions
Excel Macros
Business Uses
Downloads
Excel Tests
Latest Updates
Recent Posts
About us
Q&A
How to extract text in seconds without using any worksheet functions?
Suppose you have a list of addresses and have to extract the postal code from the list. What would you have done?
For someone with basic knowledge of Excel and only know how to copy and paste, one way to extract the data is to click into the cell, select the postal code and copy. Press the ESC key, go to the next cell and paste the cut text into the cell. Repeat the process for every address. If you are using this method, the time and effort increases as the number of address increases. This is not productive. You can use the RIGHT worksheet function if the postal code is right at the end. If not, a combination of MID worksheet function and search could do the job. That’s not what I would like to share with you here. The method I am sharing with you here is called FlashFill which is available from Excel 2013. Using this method, you can cut down the amount of time spent on extracting the postal code can be cut from hours (depending on the number of addresses) to a few seconds (irregardless of the number of the address). The time savings is at least 75% conservatively. Here’s how you use FlashFill.
Steps on using Flashfill to extract postal code
Step 1: Select the cell on the right of the address.
Step 2: Type in the postal code for the first cell in the address list.
Step 3: Select the first cell with the postal code and the rest of the empty cells below it, all the way to the last cell for the addresses.
Step 4: Go to the HOME tab and click the Fill icon. Click on FlashFill.
Step 5: Bingo! The postal codes are extracted.
If you wish to see the extraction in action, watch the video below.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
