Excel Formula Challenge Split Address
May 30, 2017
A quick backup tool for you, 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.
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?
Here's a screen shot of the fake data, and you can download the sample file to build your formulas. The full address is in column B, and the separate items are in the columns to the right of that.
If you figure out some formulas that work, send me your completed file by Friday (even if you think everyone else will send the same solution).
I'll share the solutions next week, and we'll probably see several ways to solve this challenge!
Before you do anything drastic in Excel, make a quick backup of your workbook. To do that, download a free copy of my free backup tool (BU Free).
After you install it, the Backup Free command will appear on the Excel Ribbon's Review tab. Just click the top part of the Backup Free button, to save a backup copy of the file into the same folder.
NOTE: It doesn't save your active workbook -- you can save that later, or close it without saving the changes. That's important, if you're in the middle of experimenting with something.
The free tool should be enough for most people, but if you need more features, there is a Backup Pro version -- get $5 off with coupon code CTXBUP5 (good until 11:59 PM Sunday, June 4th.
Here are a couple of recent Excel articles that you might find useful.
Shortcuts - Here are 8 handy Excel shortcuts from Tim Berty, on the Tweak Your Biz blog. I never remember #4, even though you can see that shortcut if you point to the Filter command on the Excel Ribbon. (Level - All)
Formulas -- Don't follow the examples at the top, in "16 Simple Formulas Every Architect Needs to Know". You should link to cells in your formulas, whenever possible -- don't type the numbers again in the formula! But it was fun to see FLOOR and CEILING listed -- they sound perfect for architects! You can read more about them on my site. (Level - Intro)
We went to the garden centre last week, to get some annuals for our garden. I love marigolds -- they're hardy and colourful -- so we got a bunch of those. Next, we looked for tomato plants, but I was afraid to go near these! How dangerous are those vegetables, if they need to be in a cage? Anyway, we'll stick with the "free-range" tomatoes.
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/20170530ctx.html
ddalgleish @ contextures.com
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