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 create user-defined functions in Excel

 Excel allows users to create custom functions. This enables user to extend the functionalities of Excel. In the below example, a Boolean function created to accepts a cell as a parameter and returns TRUE if the cell has color FALSE if the cell doesn't have any color. 

Let us call this function IsColor. Open VBA editor, insert a new module and paste the below code into a module.

This will create a new function IsColor. Now, you will be able to use this formula in excel sheet. In the below example cell G5 and G7 are filled with color and G6 and G8 are blank cells. Enter the formula "=IsColor(G5)" and copy the formula down through H8. 

IsColor returns TRUE for G5 and G7 and FALSE for G6 and G8.

Read More

How to make any file read-only using VBA?

VBA has in-built function SetAttr to change attribute of a file or folder. This Function is classified as File or Directory function. Below is the syntax of SetAttr and it takes two parameters.

        Syntax: SetAttr (FilePath, Attribute)

FilePath is the fully qualified path of the file and Attribute is the parameter of a file which you wish to change. In this example we are going to change attribute of the file ‘Sample.xlsx’ from normal to read-only.

Example:

Sub ReadOnly()

Dim s as String

S = C:\LM10\Sample.xlsx

SetAttr (S, vbReadOnly)

End Sub

Read More

How to add line break and tab space in outlook using VBA?

When working on email automation in Outlook, the content of the mail can be of type Body or HTMLBody. Depending on the type we select we can use html tags or VBA constants to add Line Break or Tab Space in the body of the mail.


Line Break:

Assume MailObj is the Outlook mail object. To add line break between two texts in the body of the mail we can use either VBA constant (vbCrLf) or html tag (<br/>) as shown below.

     MailObj.Body = "This is 1st text" &  vbCrLf  &  "This is 2nd Text"

or

    MailObj.HTMLBody  = "This is 1st text"  &  "<br/>"  &  "This is 2nd Text"


Tab Space:

Tab space is equivalent to 8 blank spaces. Similar to the above example tab space cab be added to each text using a VBA constant (vbTab).


MailObj.Body  =  vbTab  &  "This is 1st text" &  vbCrLf  &  vbTab  &  "This is 2nd Text"

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