Text functions in Excel for data cleansing

 

If you are a regular user of Excel and deal with lot of text data, then mastering text functions in Excel is crucial. Excel has in-built text functions to help cleansing data for you. Below are some the text functions in excel helpful for quick data cleansing

 

1.CLEAN: 

CLEAN function clears any non-printable characters from the text. Non-printable characters are first 32 to characters of ASCII table. For example in cell A1, enter the formula “=CHAR(27)” this displays a character “” as shown below, which represents escape character and it is a non-printable character.

Now, Add a text to this cell using formula below so that the cell has combination of printable and non-printable characters.

    “=CHAR(27)&” Hello”

To clean cell A1, enter the formula “=CLEAN(A1)” in cell A2.

 

2. TRIM

Trim is useful function in removing leading and trailing spaces from a text in the cell. But it doesn’t remove spaces between the words.

For example, A1 = TRIM(“     Hello”) prints Hello in cell without spaces

 

3. REPLACE/SUBSTITUTE

Both REPLACE and SUBSTITUTE functions are used to replace a specific segment of a text but has slightly different syntax. Enter a text “Hi There” in cell E4 and E5 and below are the examples of replacing the word “Hi” with “Hello” using REPLACE and SUBSTITUTE

REPLACE syntax “REPLACE(E3,1,2,"Hello")

SUBSTITUTE syntax SUBSTITUTE(E4,"Hi","Hello",1)

REPLACE uses start number, number of characters and new word to be replaced in a text as an argument, whereas SUBSTITUTE uses text to be replaces and new text and instance as arguments.

 

Read More

How to convert mailing list in Excel column into a string using Collections?

Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.

First store all Email Ids into a Collection and then combine each email Ids in Collection into a String variable and this variable can be used as Mailing List to send mails. See the below example.

Below code will create a Collection and add mail Ids from Excel sheet to it. 

Dim MailList as Collection

Set MailList = New Collection

For i  = 1 to 20

MailList.Add Cells(i,1).Value

Next i

Now we have created collection, next step is to combine all mail Ids in collection into a single string.

Dim S as String

S = ""

For Each Item in MailList

S= S  & Item  & ";"

Next 

Now we have created single string 'S' of all 20 mail Ids. This variable can be used in outlook mail  application to send mails as shown below.

MailObj.To = S or

MailObj.CC = S

Read More

How to use curly braces in non-array formula in Excel

Curly Braces are generally used in array formulas in Excel. Array formulas are entered by pressing CONTROL+SHIFT+ENTER and Excel automatically inserts curly-braces in formula. Array formulas are useful when one has to get results from multiple set of values into a single cell or range of values.

Array Formula:

For example, Consider Range A1 through A3 has text "No", "Yes" and "None". If we have to get the value of maximum length of string in cell B1, then, This can be done by Array Entering (CONTROL+SHIFT+ENTER) the below formula in cell B1.

                                                       {=MAX(LEN(A1:A3")}

which returns a value 4.

Non-Array formula with curly-braces{}

However curly-braces can be used in non-array formulas by manually inserting it. Below is an example of using curly-braces in "VLOOKUP" without array entering the formula.

                           =VLOOKUP("C",{"A",1;"B",2;"C",3},2,0)

which returns a value 3.
:-

Read More

How to remove comments only from filtered cells in Excel

Consider a column which has number one to five. Cell with odd number has a comment "ODD" and even number as "EVEN".

If you have to delete comments only from even number then, 

- Filter the even numbers in column (i.e. 2 and 4)

- Then, hit the keystroke (Alt + :) this will highlight only the filtered cells

- Then, right click and delete comment from the selected cells in context menu

To apply formats for filtered cells, select all filtered cells and use comments in paste special option to paste only the comments as shown below.

.

Read More

The Difference between Cumulative Distribution Function (CDF) and Probability Density Function (PDF)

Cumulative Distribution Function (CDF) vs Probability Distribution Function (PDF)

The Cumulative Distribution Function (CDF) of a random variable 'X' is the probability that the variable value is less than or equal to 'X'. It is the cumulative of all possible values between two defined ranges.On the other hand, Probability Distribution Function (PDF) is the probability of random variable 'X' equal to certain value. In other words it is a derivative of CDF.

Let us understand this with the example of Normal Distribution data. Normal Distribution curve is a bell shaped curve and is symmetric about its mean. its value extends from -ve infinity to +ve infinity. The curve extends indefinitely in both direction.

Consider a Normally Distributed Data with Mean = 494 and SD = 100. Let us calculate the  probability of random variable X between its mean and 500. Excel has NORMAL.DIST() function which returns CDF and PDF for a Normal Distribution. It takes four arguments namely; X, MEAN, SD and CUMULATIVE. The 4th argument CUMULATIVE is Boolean and if set to TRUE then the function returns CDF and if set to FALSE, the function returns PDF.

CDF = "NORM.DIST(500,494,100,TRUE)" = 0.5239 or  52.39%

PDF = "NORM.DIST(500,494,100,FALSE)" = 0.00 or  0%

In the above calculation CDF of 52.39% is a probability of X is from -infinity to 500. To get the probability of X between MEAN (494) and 500, subtract 50% from CDF, Hence the probability of X between MEAN and 500 is 2.39%.

Read More

How to repeat the Header row in each page using VBA?

Consider an Excel document which has employee details with 5000 employees. The 1st row of the sheet has details, such as Name, Age, Designation, Date of Birth and Date of Joining of employees. 

Now, If we want to take print of these employee list, and if we want the header row to appear in each page of the print out then, select the sheet which has the data and use the below VBA code to make header row appear on each page.

Read More

Excel Shortcuts to convert Number into different formats

Excel Shortcuts:

Keyboard shortcuts in Excel allow you to reduce the time spent doing routine task using mouse and thereby boosting productivity.

Below is a list of Excel shortcut keys to convert number into different Time and Date formats.

1) To Convert Number to Date format: Select the range of cells that contain numbers and press             CTRL+SHIFT+3

2) To Convert Number to Time format: Select the range of cells that contain numbers and press            CTRL+SHIFT+2

3) To Convert Number to Percentage format: Select the range of cells that contain numbers and           press CTRL+SHIFT+5

4) To Convert Number to Currency format: Select the range of cells that contain numbers and             press CTRL+SHIFT+4

Read More

CAGR calculation in Excel

CAGR:

CAGR - Compounded Annual Growth Rate is a measure of growth over a period of time. It is usually calculated for number of years. Businesses use CAGR to measure the growth in their revenue over a period of time. In finance CAGR is used as a measure that indicates appreciation in the value of an Investment over a period of time.

Example:

Suppose an initial investment of $10000 is made and its value appreciates to $17000 after 5 years. CAGR for this investment is calculated using the below formula.

                         

                                                          where n = no. of years

The CAGR for the above investment example is 0.11196 or 11.196%. Here, CAGR is equivalent to constant growth rate per year. (i.e. Constant growth of 11.196% for 5 consecutive years.

You can validate the CAGR by calculating Final Value using the initial value of $10000 and CAGR of 11.196%.

                                Final Value = $10000 * (1+0.11196)^5 = $17000

Read More

How to convert time format in to Minutes in Excel

How to convert time format in to Minutes in Excel:

Consider an example where Start Time, End Time and Total Time Taken for a set of activities are stored in a standard Excel Time format (i.e. hh:mm:ss). Here TOTAL TIME = END DATE - START DATE. In column F, let us calculate total time taken in Minutes.

  Now, to convert Excel time formats into Minutes, We have to multiply the values in each of columnE with 1440. So enter the formula "=E2*1440" in cell F2 to convert 1:00:00 into minutes.

Excel stores standard time formats as DAY fraction. So the value equivalent of 1:00:00 is 0.041667 days. (i.e. 1/24). Therefore 0.041667 * 1440 = 60 Minutes.

Read More

How to use SUMPRODUCT to calculate mean of Discreet Distribution

Discreet Distribution:

A Discreet Distribution is a distribution constructed from a random variable produced by non-negative whole numbers. MEAN or AVERAGE is nothing but Expected Value of a Discreet Distribution.

The MEAN of any discreet distribution is calculated by taking the sum of product of values and their probability of occurrence.In the below example, number of Diabetics out  of 5 people chosen randomly and probability of number of Diabetics in the sample.

MEAN of this discreet distribution is calculated using inbuilt function SUMPRODUCT as shown below. SUMPRODUCT takes two arguments, ARRAY1 and ARRAY2.
                               
                                                "=SUMPRODUCT(C2:C8,D2:D8)"

 The mean or the expected value of the above discreet distribution is 1.14.

Read More

How to get only Integer portion of a division using VBA operator

Excel has a built in function QUOTIENT() to return integer portion of a division. The similar result can be obtained using ROUND() function. However, VBA has operator to return only integer portion of a division. The symbol for getting division with integer portion as result is "\".

Below is an an example of two procedures with "division" (/) and "division with integer" (\) for two variables.

The first procedure returns a value of 5.5 and the second one returns 5. However, this division with integer operator does not work when used as formula in cells.

Read More

How to make Excel UserForm Immovable using VBA

By default excel userforms are movable. This will let the user to view the Excel sheet by dragging the user form aside. In case if the the creator of the user form does not want the user to view Sheets, then UserForm position can be fixed using a VBA code as given below.


                                  Private Sub UserForm_Layout()
                                  UserForm1.Left = 0
                                  UserForm1.Top = 0
                                  End Sub

Here is a link to video demonstration of this.

Read More

How to use wildcard characters in Excel formulas

Wildcard Characters:

Wildcard characters are useful when we have partial search criteria to extract or manipulate data. In Excel, we use "?" and "*" are wildcard characters. "?" represents single character, and "*" represents series of characters.


Wildcard characters are used with Excel functions, such as VLOOKUP, SUM, COUNT, SUMIF, COUNTIF and SUBSTITUTE among others. In the below Table, we have sales details of computer hardware items sold with their selling price and no. of items sold.

Product List:

We also have some scenario or conditions to extract data from the table to get desired output. Below is a picture of combination of Excel formulas and wildcard characters to get the desired result.

Scenario with Formula:

Read More

How to convert number to words in Excel

In this example let us learn to build excel template to convert a three digit number to words. Consider a three digit number say 256 in cell B1. We need to get "Two Hundred and Fifty Six" in cell C1.

First create two separate table as shown below in sheet 2 of the workbook. Values have to be entered manually for the first time.

Now get 1st, 10th and 100th digits  in cells I1, I2 and I3 respectively using RIGHT function. Now divide each number by their digit of significance and take only integer portion of it using INT function. i.e.
                        J1 = INT(I1/H1) = 6
                        J2 = INT(I2/H2) = 5
                        J3 = INT(I3/H3) = 2

       

Now do a VLOOKUP of values in cell J1, J2 and J3 in Table1 and Table2 created in STEP1 shown in picture below.

Now, in cell L1 enter the formula "=CONCATENATE(K3,K2,K1)" to get the number in to words.

Now select cell C1 in  Sheet1 of the workbook and enter ="Sheet2!L1". Now  "Two Hundred and Fifty Six" appear in cell C1.

Read More

How to create barcode in Excel

This tutorial is aimed at generating barcode equivalent of a alphanumeric number into an Excel cell. There are different types of Barcodes, such as

EAN 13,
EAN 8,
UPC A,
UPC E,
Code 128,
 ITF 14 and
Code 39.

 The first step is to decide on the right Barcode type to create (or print) depending on requirement. In this tutorial, Code 128 standard is used for illustration. Once the barcode type is decided, download the TrueType font file from google. TrueType font file is similar to a notepad file with ".ttf" extension.

After downloading the file, copy the file to "C:\Windows\Fonts" of your windows installation directory. Now, open the file copied and click on Installation as shown in the picture.

Now, open new Excel workbook select any cell type any alpha numeric number and then change the font type to Code128 from the drop down of font style as shown below.

This will generate the barcode equivalent of a number.

Read More

How to use Goal Seek in Excel

Goal Seek is one of the most important tool in Excel which helps in calculating the value input when the output is known. In other words, it calculates the value of input for the desired value of output.

Consider an Example, where a product of $ 50000 is bought on installment basis. The down payment made at the time of purchase was $ 10000. The remaining balance of $ 40000 has to be paid in monthly installment of $ 5000 each. Now, let us calculate the the number of months, the installment to paid back.

In cell B4 enter the below formula to calculate the number  of months.

                                                 "=(B1-B2)/B3"

The number of months installment to be paid is 8.

Now, let us use goal seek to determine the change in monthly installment, if the buyer decides to pay installment in 4 months instead of 8.

1) Select Data > WhatIfAnalysis > Goal Seek>

2) Goal Seek pop up window has three fields namely set cell:, to value: and By changing Cell:

      set cell:
      set cell is always a cell that contains formula. In this example it is B4.

      to value:
      Enter 4 in to value field.
   
      by changing cell:
      As we are determining the value of monthly installment for which the the no. of months is 4,               enter B3 in this field and click OK.

3) Goal Seek determines the monthly installment value and populates the same as shown below.

     Note: Goal seek does not allow the changes to multiple cells simultaneously. Use solver AddIn to      set criteria for multiple cells (or multiple variables).                        

            

Read More

How to save an Excel Workbook as HTML document?!!

Saving an Excel workbook as HTML document is necessary whenever it is required to publish reports generated in Excel on Web portals. 

The following are the steps to save an Excel sheet as HTML document.

1) Open the Excel workbook which you want to save as HTML.

2) Select File menu and click "Save As" from drop down menu.

3) Now in "Save as" window pop up, type the name of the file and in "save as type" field select
    "save as single file web page".

4) Once the "save as type" is selected, check "selection :sheet" and click on publish as
shown below.

5) Now, In "publish as" pop up window, select the sheet which you want to publish as html document and change the name and path where you want to save document. Make sure that AutoRepublish checkbox is checked (to keep original file and HTML document synchronized) and click on Publish.

6) Now, the saved HTML page can be viewed in web browser. Changes made in the original file are        automatically reflected in web page on refreshing.

Read More

How to generate dummy data in Excel

Whenever you want to test a formula or build a data model involving complex business logic, It becomes necessary to create set of sample data by yourself to work with. RANDBETWEEN function is an useful tool to generate random numbers.

syntax:

RANDBETWEEN(lower value, upper value)


Consider a scenario where 10 three digit random numbers have to be generated.
Select cell B2 and type "=RANDBETWEEN(100, 999)"

Now, drag the formula upto cell B11 to get 10 thee digit random numbers.

Random numbers change every time you refresh the sheet or workbook.

Note: This function can be used to simulate random experiments, such as tossing of coin or rolling a die. For example, use "=RANDBETWEEN(1,6)" for rolling of a die.

Read More

How to use skip blanks feature in paste special option?!?

There exists a lot of confusion among excel users on usage of "skip blanks" feature in paste special. They expect the skip blanks to filter and paste the non blank cells but, skip blanks retains the original value of the destination cell if the corresponding cell in the source cell  is blank.

Let us understand this with an example. Consider two columns A and B with integers as shown below. The cell A4 in source column is left blank.

Now, copy the values in source column, i.e. from A2 to A7. 

Select cell B2 and right click and select paste special from context menu. Check 'values' and 'skip blanks' from paste special popup window and click OK. Now all the values in destination cell changes except for the cell B4 as shown in the picture below.

Read More

How to import file names of a folder into an Excel spreadsheet

Following are the steps to import list of file names from a folder in to an Excel sheet.

1) Select a folder in which the files to be imported are saved. In this case, I've selected AV files folder on my desktop with 4 files in it.

2) Now, hold the Shift key on keyboard and right click on the folder and then select "Open command window here" from the context menu as shown in the picture above.

3) Now the command window pops up with the present directory. Type "dir / b>filenames.xls" in the command prompt and hit Enter.

4) The new Excel workbook "filenames.xls" has been created in AV files folder with the list of file names in it as shown in the picture above.

Read More