Basic Excel Functions you should know to make your work life easier

As an IT Professional you may have to deal with lots of data every day. I have seen many experienced IT admins spending quite lots of time on the activities which can be quickly performed in Excel if you know few basic Excel formulas.

I will explain most commonly used Excel functions here.  Let’s start with the String Functions.

String Functions:

Microsoft Excel offers many functions to manipulate string data.

1. LEFT()

LEFT Function allows you to extract the leftmost characters from a string.

Syntax : =LEFT(Text,Num_Chars)

• The Text parameter refers to the string you want to manipulate
• The Num_Chars parameter refers to the number of characters from left side of string you want to extract.

Example:

In the below example, we have extracted first 4 characters from left.

2. RIGHT() RIGHT Function allows you to extract the rightmost characters from a string. Syntax :  =RIGHT(Text,Num_Chars)

• The Text parameter refers to the string you want to manipulate
• The Num_Chars parameter refers to the number of characters from right side of string you want to extract.

Example: In the below example, we have extracted last 8 characters from string.

3. MID() MID Function allows you to extract the characters from middle of a string. Syntax :  =MID(Text,Start_Num, Num_Chars)

• The Text parameter refers to the string you want to manipulate
• The Start_Num parameter refers to the numeric start position for extraction with in source string.
• The Num_Chars parameter refers to the number of characters you want to extract from Start Position of source string.

Example: In the below example, we have extracted a word ‘EXAMPLE’ from source string. Start Position :12 No Of characters to extract: 7

4. LEN() Len() function return the length of source string in number of characters. Syntax :  =LEN(Text)

• The Text parameter refers to the string which length you want to know.

Example:

5. FIND() FIND() function allow you to know the starting position of certain character / word with in source string. The function return the starting position of character / word with in source string. Syntax :  =FIND(find_text,within_text)

• The Text parameter refers to the sub string which you want to find in source string.
• The Within_text parameter refer to the source string where you want to find a text.

Example:

Note: Please note that FIND() function is CASE SENSITIVES. We have another function ‘SEARCH()’ in Excel which is not case sensitive. The syntax of functions are same. Hence you can use them based on your requirements. 6. TRIM() The Excel TRIM() function removes extra spaces from source string. The TRIM function removes extra spaces from beginning of string, end of string, and between the string leaving only single space between words. Syntax :  =TRIM(text)

• The Text parameter refers to the string which you want to truncates.

Example:

Real world examples: Now, you are familiar with basic string functions. Let’s go through few real world examples utilizing combination of above functions to understand them better. Example 1: FIND and LEFT You need NETBIOS  name for 1000+ computers. However, the list which you received contains computer FQDN. The challenge here is that Netbios name length is different for each computers. Hence you can’t rely solely on LEFT() function. The solution would be to dynamically find number of characters which you want to extract. Once you know the number of characters to be extracted, you can use LEFT() function to extract the characters. See the approach below which we can use in this scenario.

• Find the position of first “.” in given string. The position number returned will always 1 more than length of Netbios name.
• Nest the Find() formula with in LEFT() function. So, instead of providing no of characters to extract, you have to nest FIND() function with in LEFT function to dynamically find number of characters to be extracted.

Example 2 : TRIM You received list of employee which are in First Name and Last name format. However, you need to present that in Display Name format. Both first name and last name contains extra spaces and when you join them using & the result does not looks good. Please see the example in first picture.

Now you can use TRIM function to rescue your self. Please see the example below to understand how you can use TRIM function along with ampersand (&) Operator to join the string after truncates extra space using TRIM function.

Stay tuned. We will publish more articles to give you more examples (easy and complex) from real world. You can subscribe to the blog if you want an alert when a new article published. The Excel workbook used for above examples are available here for your reference.

Related Posts:

• String Functions : UPPER, LOWER & PROPER | Microsoft Excel
• How To Join Texts Using TEXTJOIN & IF Functions | Microsoft Excel
• Basic Excel Functions you should know to make your work life easier
• Powershell – Merge CSV files & Insert file name as a column
• Microsoft Excel – Vlookup Explained