Microsoft Excel Functions Practice Text

 Text Functions 

LEFT, MID, RIGHT

LEFT, MID, RIGHT are Excel’s most basic Text Functions that enable us to extract a specific part from a text string.

For example, we can use Text Function to extract the first three letters of a word, the last 5 letters or 10 letters from the middle of the sentence.

LEFT is used to extract the leftmost letters of a text/cell, RIGHT does the same for the rightmost letters and MID works from the middle.

Each of these functions can extract a specific amount of letters that will be defined by the user when writing the function.

Let’s see how it works!

CONCATENATE Function

The CONCATENATE function in Excel allows us to merge text from multiple cells into one cell. This can be useful for creating a single, combined string of text from several sources.

Syntax, The syntax of the function, is simple: =CONCATENATE(text1, text2, text3…) text1, text2, text3, etc. – the text strings we’d like to combine into one cell. These can be individual cell references or direct text entries in quotation marks. Note that the “=CONCATENATE()” function can only combine text and cannot be used to perform calculations.

It is worth noting that there are newer functions that may be better alternatives for combining text, such as the =TEXTJOIN and =CONCAT functions, or use the Flash Fill.

Example

For example, if we have text in cells A1, B1, and C1, we can combine these into one cell by entering “=CONCATENATE(A1,B1,C1)” into another cell. This will result in a single cell that contains the combined text from all three cells.

LEN

LEN function is used to discover the LENGTH of a cell – It allows us to know the number of characters in a certain cell.

Let’s see how it works:

Mary – 4 characters

had – 3 characters

a – 1 character

little – 6 characters

lamb – 4 characters

Total – 18

So how did we get 22 characters?

It is important to note that the SPACE character is considered a character, just like any other letter or digit in Excel. Therefore it is also counted in the total number of characters. 

We have 5 words, which means we have 4 SPACE characters which should be added to our calculation!

18 characters + 4 spaces = 22 characters! 🙂

TRIM

TRIM function is used for the removal of extra space characters. 

When the excess spaces appear between two words, the function keeps only one space character.

Example

Notice the extra spaces removed from the beginning of the cell, as well as the spaces that were removed between “Hello” and World!”, except a single space character that was kept. 

TEXTJOIN

TEXTJOIN function allows users to combine multiple cells together easily.

Unlike previous Excel functions such as CONCATENATE, which require the user to repeat the delimiter (such as space) between every cell – TEXTJOIN can combine a range of cells using a single delimiter!

Important note – TEXTJOIN function is available in Excel 2019 and in Excel 365 versions. This is one of the greatest reasons why you should upgrade your Excel version ASAP (Or just use Excel Online). This is absolutely one of the greatest new Excel functions! 🙂