Do you use this Excel function?
Sept 19, 2017
An little-known Excel function, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
Do you ever use Excel's BASE function (in Excel 2013 and later)? I didn't, until UniMord shared a useful trick that you can do with BASE. Thanks!
UniMord creates files in Excel, and saves the data in fixed-width text files. To make ID numbers, and other data fields, the correct length, he used the TEXT and REPT functions. For example, this formula converts the number in cell A2 to a 13-digit string, with leading zeros.
That's like using the TEXT function, and typing 13 zeros as the number formatting string.
Then, UniMord found that the BASE function can do the same task on its own. It will convert a number from Base 10 (our decimal system), to a specific-length string, with leading zeros.
P.S. The BASE function makes me think of this bad "All your base" translation from an old computer game.
To add a line break in a single cell, click where you want the line break, then press Alt + Enter. To remove that line break later, click at the end of the top line in the cell, and press Delete.
If you want to remove line breaks from many cells at once, there are step-by-step instructions on my site. The key step is what you put in the Find and Replace boxes:
And thanks to Koen vT, who sent this tip: use Find and Replace to add line breaks for troubleshooting long, nested IF formulas. NOTE: Your formulas might use a semi-colon instead of a comma:
After adding the line breaks, make the formula bar taller, so you can see most of the formula.
Here are a couple of recent Excel articles that you might find useful or interesting.
Shortcuts: In a short video, Mike Girvin shows different ways that you can use the Ctrl+Enter and the Ctrl+D shortcuts in Excel. Are you like "the vast majority" that Mike mentions at 1:30? (Level - All)
Last Row - Charles Williams shows different ways to find the last row with macros in Excel. One of the problems is deciding what kind of "last row" you need! You might know Charles from his FastExcel add-in, and all his work with calculation speed. (Level - Int/Adv)
Excel Charts - Jon Peltier's Excel Charting Utility is a great tool, if you do lots of work with charts. A new Demo version is available now, with a trial period of 2 weeks. That gives you time to test all its features, such as Marimekko charts, and fancy Waterfalls, to see if it's what you need. (Level - Int/Adv)
It's a sign that your life is boring, when the highlight of the week is gettng a new office chair. But, when you sit at a desk all day long, your chair is a vital part of your equipment!
My old chair did its job well, for about 15 years, even though it looks a bit ragged now. But, the left arm kept falling off, so it was finally time to let it go. The good news is that I found almost the same chair again. The bad news is that all the knobs and controls are in a different place!
That's it for this week! If you have any comments or questions, send me an email.
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/20170919ctx.html
ddalgleish @ contextures.com
Last updated: September 22, 2017 10:54 AM