Excel Fancy Number Format
Jan 16, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
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.
Or, use a formula, with the TEXT function to format the date -- there are examples on my website.
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.
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.
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.
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)
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.
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.
ddalgleish @ contextures.com
Last updated: April 9, 2020 3:24 PM