Tips and keyboard shortcuts for text and date functions in Excel

Keyboard Shortcuts

The CTRL (CMD) key When you are using a function like CONCAT to join text from multiple cells, hold down the CTRL key while selecting the cells and Excel will automatically insert the comma between each cell reference for you. For example, if you want the formula =CONCAT(A2,D2,G2) then hold down CTRL while you click on cell A2 then D2 then G2.

CTRL + ; Inserts today’s date as a fixed value. (Note that this is different to the =TODAY() function because this date is fixed and will not change when you come back to your workbook tomorrow or next week.)

CTRL + SHIFT + ;| CMD + ; Inserts the current time as a fixed value. (Note that this is different to the =NOW() function because the time is fixed and will not change when you come back to your workbook an hour or a month later).

F4 | CMD + T Toggle between relative and absolute references.

------------------------------------------------

Windows Shortcuts: Microsoft Office Support pages | Mac Shortcuts: Microsoft Office Support pages

Excel Terminology

The anatomy of a function

Let’s look at the MID function as an example: =MID(text, start_num, num_chars)

= [equals] Every function must start with an equals sign.

MID [FUNCTION NAME] The standard syntax is to use upper-case letters, but the function will still work in lower-case letters!

( [Open bracket] After a function name you must have an open bracket, "(", after this open bracket you can start entering your arguments.

text, start_num, num_chars [arguments] These are the arguments that we input into a function. An argument is an input into a function, where a function reads this argument, and other arguments if needed, to process the function. You can directly type in arguments yourself or you can have other functions calculate the values of the arguments for you. We explain this latter bit under nested functions below.

A2 [Cell reference] Within a function, depending on the function, you may either need to specify a cell reference or you may need to specify a range. If a range is needed (not for this function), a range would be specified in the syntax: A2:A10. Here A2:A10 which reads in natural language as A2 to A10, in other words, the colon, ":" is the natural language equivalent of "to".

, [comma] Commas are used to separate arguments within a function. This way, Excel knows that the input of one argument has ended and the input of the next argument is beginning. Some functions have only one argument, and thus, a comma will not be needed.

) [Close bracket] At the end of the function, you must have a close bracket, ")", then Excel knows that you have stopped inputting arguments. In the latest versions of Excel, you can get away without typing the last bracket and then pressing Enter and Excel will automatically add this bracket for you.

Nested Functions

We can also have a function inside another function, where a function is used as an argument, such as:

=MID(A2,2,FIND(" ",A2))

Here, the FIND function is used within another function, MID, this entire then becomes a nested function. Excel will work the innermost function first and gradually work its way outwards. Inner functions are sometimes called helper functions.

Here, FIND(" ",A2) is used as the third argument of the MID function which should be num_chars, i.e. the number of characters. In other words, instead of us telling Excel the number of characters, and this value being static, the helper function, FIND, is telling Excel to find the number of characters, and hence this value becomes dynamic.

CONCAT, CONCATENATE and &

The CONCAT function joins text together. The text being joined can be entered by referring to other cells, e.g. =CONCAT(B4,B3), or the text can be typed directly =CONCAT("John","Smith"), the comma specifies what you would like to join. Remember that when inputting text into a function use " " around the text.

The & works in a similar way. It can be viewed that & is what tells Excel to join the text so =B4&B3 joins B4 and B3. The same rules apply if the reference is made to text directly ="John"&"Smith". Remember that & is used as part of a formula so you need to start with =.

CONCAT is the newer version of CONCATENATE function which became available in the Office 365 version of Excel. It does everything that CONCATENATE does and it also supports using a range as an argument, for example, =CONCAT(A5:A20). CONCATENATE is still available in the newer versions of Excel but you will not get the extra functionality.

CONCAT became available in the January 2016 version of Excel, which is only available if you have the Office 365 version of Microsoft Office. You can read the Release Notes for the various versions of Excel and you can also find out which version of Excel you are using.

Text to Columns

Open the file W2_Extracting_Text, where we want to extract the Floor, Wing and Extension from Column K that contains all this information under Location.

Select the Location column, go to the Data tab and click on Text to Columns. You should get a dialog box that looks like this where you can select Delimited or Fixed Width.

For Step 1, let’s start with Delimited and click Next.

In Step 2 above, check Space and Other, and type "-" next to Other. Here, Excel will split the string of text in the Location column whenever it sees a space, " ", as well as whenever Excel sees a hyphen, "-".

Now click Next and you will see Step 3 as below:

Here you can specify the data format. Since ours is the default, General, we just need to click Finish. Excel will then warn you that there is already data, and ask you whether you want to replace it. Click OK.

Now you should see the Location data split into 3 columns in your spreadsheet – the floor, the wing, and the extension.

Another option to split the text into columns in Excel is to choose Fixed Width in Step 1 and then click Next. Then Step 2 will appear as below:

You will then need to click within Data preview to add arrow markers to split the text according to a fixed width.

This works well if the data follows a fixed length pattern like it does for the characters of the floor. However, this does not work well for the wing (as you can see above) which sometimes has 4 characters, and at other times has 5 characters.

Text to columns is a great tool for one-off changes, and when you do not need to retain the original raw data. However, for more automated dynamic changes that need to occur as our spreadsheet is populated, functions are much more useful, as we discuss in this week’s Practice Videos.

Ninja Tip of the Week

TEXTJOIN

TEXTJOIN is another function that can be used to join text together, this works well because of the following:

1. You can specify once that you want a space between each word and don’t have to include a space each time like we did in CONCAT
2. You now have the choice to ignore empty cells in a range.

For example =TEXTJOIN(" ", FALSE, "JOHN", "SMITH") returns JOHN SMITH. The first argument specifies the separator you would like to see between each word (a space in this instance), the second argument specifies whether to ignore empty cells or not, and then the text follows. You can specify the text as a range, so =TEXTJOIN(" ", TRUE, A5:A12) is also valid and the text is contained in the specified range.

Like CONCAT, TEXTJOIN is only available in the latest Office 365 version of Excel.

Inserting a line break within text functions

In the course Excel Skills for Business: Essentials we saw that you can enter a line break inside a cell using the shortcut ALT + ENTER (or CTRL + OPTION + RETURN for Mac), however this shortcut will not work when you want to include a line break inside a text function. For example, say we have been given the data in cells A2 and B2 below, and we want to join them on two lines in cell C2.

We can insert a line break using the function CHAR(10), so cell C2 could be entered as either of the following functions:

=A2&CHAR(10)&B2

=CONCAT(A2,CHAR(10),B2)

Remember to turn on "Wrap Text" formatting for cell C2, otherwise it will display with all of the text on a single line.