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 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

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