Contextures News

Excel Split Address Formulas

June 6, 2017

Formulas for splitting an address into separate columns, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Formula Challenge Solution

In last week's newsletter, you had a challenge -- If a worksheet has full addresses in one column, what formulas would you use to get the street address, city, state and zip code into separate columns?

Thanks for sending your solutions -- I appreciate it, and it was amazing how many different formulas you used! I've added a Split Address page on my website, to explain how some of the formulas work. You can also download the solutions workbook there, or click here to download it.

Examples

Here are example formulas -- one for each of the fields. LEFT, RIGHT, and MID were key functions in this challenge, with FIND or SEARCH to get character positions:

• Street Address: =LEFT([@FullAddress], FIND("-",[@FullAddress])-2)
• City: =MID([@FullAddress],LEN([@StreetAddress])+4, FIND(",",SUBSTITUTE([@FullAddress],[@StreetAddress],""))-4)
• State: =MID([@FullAddress],FIND([@City],[@FullAddress])+LEN([@City])+2,2)
• Zip Code: =MID([@FullAddress],(FIND([@State],[@FullAddress]))+3,9)

Flash Fill or Formulas

In last week's challenge, a few people said that they would use Flash Fill, instead of formulas, to split the addresses. Flash Fill was introduced in Excel 2013, and it helps you extract information, based on patterns in your data. You can use it to put names in reverse order, split dates and times, and many other data tasks.

Flash Fill isn't right for every job, but is helpful in some cases:

• Flash Fill Pros: Quick and easy way to extract data. No formulas to slow down a big workbook.
• Flash Fill Cons: Items don't automatically change if the original data is updated. Data needs a very consistent pattern (no IF/OR options that a formula could provide).

Here's how to use Flash Fill in the Split Address challenge:

• Enter the address information in separate columns, in the first two rows (Type an apostrophe at the start of the zip code, in case any start with a zero)
• Then, click in cell C6, go the Data tab of the Excel Ribbon, and click Flash Fill.
• Repeat for cells D6, E6 and F6.

Note: The screen shot is an animated gif, so it might not work in email.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Charts - You can build lots of things in Excel, but should you? The Science Goddess asked that question when she created a unique chart for a co-worker. (Level - All)

Cleaning Data -- If you want to learn more about cleaning data, there is a free course on the Learno.net website -- Cleaning Data in Excel, led by data journalist Maarten Lambrechts. It's about 1.5 hours in total, with 3 modules that cover formulas and pivot tables, and no programming. (Level - Intro/Intermediate)

Also see: My Excel Products || Excel Events || Previous Issues

Pie Chart Shirt

On Friday, I was at Microsoft Canada for MVP Day. The commute was easy, because it's only 5 minutes from my house. It was great to see my friend, Ken Puls, there too, all the way from Vancouver Island. Ken is a Power Query expert, and gave me a signed copy of his book, M is for (Data) Monkeys. Do you like his pie chart shirt? It says, "Colors on this Shirt - Black, White"

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20170606ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.