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.
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.
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:
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:
Here's how to use Flash Fill in the Split Address challenge:
Note: The screen shot is an animated gif, so it might not work in email.
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
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.
Last updated: July 21, 2017 9:40 AM