Contextures

Contextures News

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!

All Your BASE

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.

=TEXT(A2,REPT(0,13))

That's like using the TEXT function, and typing 13 zeros as the number formatting string.

=TEXT(A2,"0000000000000")

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.

=BASE(A2,10,13)

Read more about the TEXT function and REPT function on my website.

base function

P.S. The BASE function makes me think of this bad "All your base" translation from an old computer game.

Line Breaks

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:

  • In the Find box, press Ctrl + J
  • Leave the Replace box empty.

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:

  • In the Find box, type a comma, then type IF(
  • In the Replace box, type a comma, then press Ctrl+J, then type IF(

After adding the line breaks, make the formula bar taller, so you can see most of the formula.

Excel Articles

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)

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

A New Chair

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!

weekly photo

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: http://www.contextures.com/newsletter/excelnews2017/20170919ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

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.

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: September 22, 2017 10:54 AM