The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,617 other followers

Archive for the ‘Excel’ Category

ms office – Keyboard shortcut to select all text in a cell in Excel – Ask Different

Posted by jpluimers on 2019/06/10

Via [WayBack] ms office – Keyboard shortcut to select all text in a cell in Excel – Ask Different a few keyboard tips.

Lets start with the shortest one:

  1. Put the focus on the cell (click, use arrow keys, etc)
  2. Press space
  3. Press CommandZ or ControlZ to undo the change

This probably is unintended, but works great: all text is now selected, so you can copy/cut with Command-C/CommandX.

Now the “official” way:

  1. Put the focus on the cell (click, use arrow keys, etc)
  2. Press ControlU or F2 to edit the cell (the cursor is now at the end)
  3. Press ShiftAltHome or ShiftControlHome (to select all text)
    note: Home can also be FnLeft.

Other selections you can make while the cell is in edit mode:

  • Press ShiftAltEnd or ShiftControlEnd (to select to the end of the cell)
    note: End can also be FnRight.
  • Press ShiftAltRight or ShiftControlRight (to select one word to the right)
  • Press ShiftAltLeft or ShiftControlLeft (to select one word to the left)
  • Press ShiftAltDown or ShiftControlDown (to select to the same position on the line below)
  • Press ShiftAltUp or ShiftControlUp (to select to the same position on the line up)

Keyboard symbols (more at [WayBackCommand, Option, & Shift Symbols in Unicode):

  • Shift
  • ^Control
  •  – Alt which is the same as Option
  •  – Command
  • Fn – Function

–jeroen

Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, MacMini, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »

keyboard – How to add a line break in a cell in Excel for Mac – Ask Different

Posted by jpluimers on 2019/06/03

What key combination do I have to press to create a line break in a cell in Excel for Mac 2011? The Windows combination of Alt+Enter does not work on the Mac.

Source: [WayBackkeyboard – How to add a line break in a cell in Excel for Mac – Ask Different

The answer depends on the Excel for Mac OS X version you are using.

Excel 2015 is simple (thanks esham): use Option+Enter.

In older Excel <= 2011 (thanks nwinkler), use Command+Option+Enter or Control+Option+Enter.

Some users report the also need the Fn key in addition to the above modifiers.

–jeroen

PS: Later I found out that [WayBack] Beckism.com: Use a linebreak in Excel on Mac also shows the Excel <= 2011 solution Control+Option+Return (note that Return is the same key as Enter).

Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »

VLOOKUP Example Spreadsheet & Tutorial | Productivity Portfolio

Posted by jpluimers on 2018/08/10

Because I tend to forget how to use this:

Excel VLOOKUP tutorial with an example spreadsheet & video. The article shows how to lookup the values from one column to use in another worksheet column.

Source: [Archive.isVLOOKUP Example Spreadsheet & Tutorial | Productivity Portfolio

–jeroen

Read the rest of this entry »

Posted in Excel, Office, Power User | Leave a Comment »

Convert a text value into a date in Excel. Learn Microsoft Excel | Five Minute Lessons

Posted by jpluimers on 2017/01/09

To match dd/mm/yyyy and mm/dd/yyyy column values in columns C (Date1) and N (Date2), I used these formulas in row 2:

Meaning: Column: Formula: Explanation:
Date1Text I =TEXT(C2,”dd/mm/yyyy”) Interpret date text of the original text no matter the Excel settings
Date1Value J =DATE(RIGHT(I2,4),MID(I2,4,2),LEFT(I2,2)) Encode the date text to an actual date: dd#mm#yyyy format where # is any separator
DatesEqual K =J2=L2 Are the encoded dates equal?
Date2Value L =DATE(RIGHT(M2,4),LEFT(M2,2),MID(M2,4,2)) Encode the date text to an actual date: mm#dd#yyyy format where # is any separator
Date2Text M =TEXT(N2,”mm/dd/yyyy”) Interpret date text of the original text no matter the Excel settings

 

Learn how to convert text values into dates in Excel. This lesson covers a range of different scenarios to help you. The comments have even more examples.

Source: Convert a text value into a date in Excel. Learn Microsoft Excel | Five Minute Lessons

Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | 1 Comment »

datetime – Round time to nearest 15min interval in Excel – Stack Overflow

Posted by jpluimers on 2016/10/14

Easy, but I always forget:

= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440

Thanks David for answering.

–jeroen

via datetime – Round time to nearest 15min interval in Excel – Stack Overflow.

Posted in Excel, Office, Power User | Leave a Comment »

 
%d bloggers like this: