Excel Formula and Functions-2

 

Excel Formula and Functions 

11.REPLACE

As the name suggests, the REPLACE() function works on replacing the part of a text string with a different text string. 

The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicate the number of characters you want to replace. 

Let’s have a look at the ways we can use this function.

·         Here, we are replacing A101 with B101 by typing

      REPLACE =REPLACE(A15,1,1,"B")

 

   

Fig: Replace function in Excel

·         Next, we are replacing A102 with A2102 by typing:

        “=REPLACE(A16,1,1, "A2")” 

Fig: Replace function in Excel

·         Finally, we are replacing Adam with Saam by typing:

        “=REPLACE(A17,1,2, "Sa")” 

 

Fig: Replace function in Excel

Let’s now move to our next function. 

12. SUBSTITUTE

The SUBSTITUTE() function replaces the existing text with a new text in a text string. 

The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.

Here, [instance_num] refers to the index position of the present texts more than once. 

Given below are a few examples of this function:

·         Here, we are substituting “I like” with “He likes” by typing: 

        “=SUBSTITUTE(A20, "I like","He likes")” 

  

Fig: Substitute function in Excel

·         Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.

Fig: Substitute function in Excel

·         Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”.

Fig: Substitute function in Excel

That was all about the substitute function, let’s now move on to our next function. 

13. LEFT, RIGHT, MID

The LEFT() function gives the number of characters from the start of a text string. Meanwhile, the MID() function returns the characters from the middle of a text string, given a starting position and length. Finally, the right() function returns the number of characters from the end of a text string.  

Let’s understand these functions with a few examples.

·         In the example below, we use the function left to obtain the leftmost word on the sentence in cell A5.

Fig: Left function in Excel

Shown below is an example using the mid function.

Fig: Mid function in Excel

·         Here, we have an example of the right function.

Fig: Right function in Excel

14. UPPER, LOWER, PROPER

The UPPER() function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the other will be in lowercase.

Let’s understand this better with the following examples:

·         Here, we have converted the text in A6 to a full uppercase one in A7.

Fig: Upper function in Excel

·         Now, we have converted the text in A6 to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

·         Finally, we have converted the improper text in A6 to a clean and proper format in A7.

Fig: Proper function in Excel

Now, let us hop on to exploring some date and time functions in Excel.

15. NOW()

The NOW() function in Excel gives the current system date and time.

Fig: Now function in Excel

The result of the NOW() function will change based on your system date and time.

16. TODAY()

The TODAY() function in Excel provides the current system date.

Fig: Today function in Excel

The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.

Fig: Day function in Excel

The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December.

 Fig: Month function in Excel

The YEAR() function, as the name suggests, returns the year from a date value.

Fig: Year function in Excel

17. TIME()

The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.

Fig: Time function in Excel

18. HOUR, MINUTE, SECOND

The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.

Fig: Hour function in Excel

The function MINUTE(), returns the minute from a time value as a number from 0 to 59.

Fig: Minute function in Excel

The SECOND() function returns the second from a time value as a number from 0 to 59.

Fig: Second function in Excel

19. DATEDIF

The DATEDIF() function provides the difference between two dates in terms of years, months, or days.

Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.

Fig: Datedif function in Excel

Now, let’s skin through a few critical advanced functions in Excel that are popularly used to analyze data and create reports.

20. VLOOKUP

This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify. 

Below are the arguments for the VLOOKUP function:

1. lookup_value - This is the value that you have to look for in the first column of a table.
2. table - This indicates the table from which the value is retrieved.
3. col_index - The column in the table from the value is to be retrieved.
4. range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

We will use the below table to learn how the VLOOKUP function works.

If you wanted to find the department to which Stuart belongs, you could use the VLOOKUP function as shown below:

Fig: Vlookup function in Excel

Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup. 

If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing department.

21. HLOOKUP

We have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.

Below are the arguments for the HLOOKUP function:

1. lookup_value - This indicates the value to lookup.
2. table - This is the table from which you have to retrieve data.
3. row_index - This is the row number from which to retrieve data.
4. range_lookup - [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.

Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.

                                                     

Fig: Hlookup function in Excel

Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.

Once you hit enter, it will return “New York”.

Our Data Analyst Master's Program will help you learn analytics tools and techniques to become a Data Analyst expert! It's the pefect course for you to jumpstart your career. Enroll now!