Contextures

Contextures News 20180116

Excel Fancy Number Format

Jan 16, 2018

Create a fancy number format, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Fancy Formatting

If you enter a date in a cell, you can choose a Date format on the Home tab of the Excel Ribbon. Or, use the keyboard shortcut Ctrl+1 to open the Format Cells window, and choose a format there.

If you want to show some text with the date, use a custom number format. This leaves the result as a real number, which could be used in other formulas, such as a total.

  1. Right-click the cell that you want to format, and click Format Cells
  2. On the Number tab, click on the category that you want for the number, such as Date
  3. Select the formatting, in the list at the right
  4. Next, click the Custom category
  5. In the Type box, type the text inside double quote marks, before and/or after the date format. For example: "Today is " d-mmm-yy ". Enjoy!" Remember to include spaces between text and numbers.
  6. Click OK, to apply the formatting

scroll through filter items

Or, use a formula, with the TEXT function to format the date -- there are examples on my website.

SUBSTITUTE and TRIM

Last week, you saw a couple of SUBSTITUTE formulas, which counted items in a cell, based on comma separators. If you're using space characters, it's important to include the TRIM function -- it removes any leading, trailing, or extra space characters.

In the screen shot below, the formula in column B uses TRIM, and gets a correct count.

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

There's no TRIM in column C's formula, so the counts in C4 and C5 are incorrect, due to extra spaces in A4 and A5.

SUBSTITUTE function

Thanks to UniMord and Andrew H, who reminded me to mention TRIM!

See the new SUBSTITUTE examples on my Contextures Blog, and get the download file too.

Excel Articles

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

Security - A recent security update has given admins the ability to disable Excel's Dynamic Data Exchange (DDE) prompts. Woody Leonhard explains the changes, and where you can find the details - they're in an old security bulletin! (Level - All)

Data Viz - The Science Goddess shares her latest data viz, and the steps to build it. It's not Excel, but might spark some ideas for new ways to present data concisely. (Level - All)

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

Non-Productivity Tools

Usually I step away from my computer to take photos, but not last week. This popup appeared while I was working on an Excel file, and I have no idea what prompted it. Why does Windows 10 think I'm bored, so early in the day? And why isn't there an easy way to turn off this type of "help"? Grrrr.

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180116ctx.html
I'll also post any article updates or corrections there.

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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 4, 2018 3:28 PM