Tricks and Troubleshoots

of Microsoft Excel

Rajib Biswas | 23 May 2020

Table of Contents:

Table of Contents
    Add a header to begin generating the table of contents

    Essentials

    Microsoft Excel is undoubtedly among one of the most used Office tools for analysis and knowing shortcut tricks may sometimes save valuable time and increases the productivity. In this post, I’ll describe simple to do tips and tricks to excel at Microsoft Excel. I’ll continually update post anout things that I tried to figure out and finally found ways to execute them.

    How to copy first and last name from a cell in Excel?

    This is commonly used for extracting First name and Last name from a column that contains full name of people. But I had a different use-case. I had a list of duration of a task from my office. My assignment was to calculate the time difference between them. It was formatted in this way:

    So, to get the duration in minutes, I first decided to extract the staring time, then extract the ending time in another column and then calculate the time difference. So in the column B, I need to extract the first word and in the column C, I need to extract the last word from column A.

    To extract the first word/name, the formula is: =IF(ISERR(FIND(" ",A2)),"",LEFT(A2,FIND(" ",A2)-1))

    And to extract the last word/name the formula is: =IF(ISERR(FIND(" ",A2)),"",RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

    So from there I have got this:

    In case you need to extract the nth number of a text string, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. Then, click Insert > Module, and paste the following code in the Module window.

    Function FindWord(Source As String, Position As Integer)
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function
    Save the code and now if you want the 4th word from A2 cell, use this formula in =FindWord(A2,4) into a blank cell B2, and then drag this cell’s AutoFill handle to the range as you need.

    How to calculate time difference in Excel?

    So, we are not done yet with our above table. We now want to calculate the time difference between those starting and ending time in h:mm format.

    Formula to calculate the time difference between two times in h:mm format is (in column D): =TEXT(C2-B2, "h:mm")

    We used this formula in column D. And not, to get this h:mm time in minutes, the formula is (in column E): =D2*1440

    So, our final table is:

    If a cell contains specific text in Excel

    I had collected some research data with a Google Form questionnaire. There, I had a multiple choice input for the diagnosis of the patient. But later on, when I needed to analyze the data, I wanted to code them with my preferred diagnosis (if existed) as ‘1’ and the rest of them as ‘0’. So, I used to the following formula.

    Formula to see if a cell contains specific text in excel: =IF(ISNUMBER(SEARCH("*Recurrent UTI*",A2)),1,0)

    IFS function. Cell value based on another cell value

    I was calculation DASS-21 scores of one of my research participants. 21 questions are asked to the participants and based on their answers, they are scored what is their Depression, Anxiety and Stress status. If the participant had Depression score 0-4, he/she fall into the category of ‘Normal’, if score is 5-6, the category will be ‘Mild’. So Excel comes handy assigning category of the participants based on their scores.

    Suppose, you have Depression score ‘D’ in the column A and want to generate its Depression Status in column B. This is the formula do it. =IFS(A2<=4,"Normal",A2<=6,"Mild",A2<=10,"Moderate",A2<=13,"Severe",A2>13,"Extremely Severe")

    How to Find Duplicate Row in Excel

    In some cases, you may have some duplicate rows in your spreadsheet and you may need to find them and delete them. The straightforward way to do that is by conditional formatting. And if your information is distributed along several rows, the best way to combine the rows and then do a conditional formatiing.

    Let’s say, you have some entries of first name and last name of some persons and there are some duplicate values. You want to find the duplicate rows. First create a new column named Combined, then combine column A and B using =A2&B2. Then go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. A pop up will appear, click OK. All the duplicate cells will be highlighted. Now delete the cells that you don’t need.
    A quick tip for Google Sheet. To highlight the duplicate value in Column C in Google Sheet, Go to Format > Conditional formatting > Format rules > Custom formula is  > Write =COUNTIF(C:C,C1)>1 > Hit Done

    Share this article!

    Read Other Recent Posts