Contextures

Contextures News

Excel Enter Key Shortcuts

June 13, 2017

Formula to find a specific character in a cell, 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.

Find Nth Character in Cell

Last week, we used FIND or SEARCH to get the position of a hyphen in an address. Those functions find the first occurrence of a character, but what formula could you use to find the Nth occurrence?

For example, how would you find the 3rd period in each of the codes shown below? My answer is below the screen shot, so scroll down when you're ready to see it.

find nth occerrence of character

Hint: The SUBSTITUTE function lets you work with a specific instance of a character -- a very useful feature!

substitute function instance number

  • Use the SUBSTITUTE function to change the 3rd period into a unique character, such as "|".
  • Then use FIND to locate that unique character.

Here is the formula that I used, to get the position of the 3rd period in cell B2:

=FIND("|",SUBSTITUTE(B2,".","|",3))

Enter Key Shortcuts

When you press the Enter key on an Excel worksheet, it usually moves you down one row. You can change that setting in the Excel Options, in the Advanced category -- choose Down, Right, Up or Left. Or, remove the check mark, so you stay in the same cell.

No matter which direction you select, you can use these shortcuts with the Enter key

  • Press Shift + Enter to go in the opposite direction
  • Press Ctrl + Enter to stay in the same cell

Find more Data Entry shortcuts on my website.

move after pressing Enter key

Excel Articles

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

Charts - A Histogram is one of the new chart types in Excel 2016, and Nathan Yau explains how Histograms work, in simple terms. Jon Peltier's Chart Utility has a nice Histogram feature too. (Level - All)

Power BI - There's a free online Introduction to DAX course from Marco Russo and Alberto Ferrari. You'll learn how to build formulas and expressions in Power Pivot and Power BI. (Level - Intermediate / Advanced)

Training - Learn more about Pivot Tables or Power Query, in the free 1-hour webinars that John Michaloudis is hosting. Pick a date and time that fit your schedule.

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

Canada 150

It's less than a month until we celebrate Canada's 150th birthday, so here's an old family photo, to get you in the holiday spirit. We were visiting Ottawa, long ago, and my son wanted to pat the Mountie's horse. My daughter didn't seem quite as eager to do that!

weekly photo

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

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:40 AM