masthead for advanced excel

How to extract text in seconds without using any worksheet functions?

extract postal codes from these addresses 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.


facebook-pic"

New! Comments

Have your say about what you just read! Leave me a comment in the box below.