4 Amazing WhatsApp features you should know

A lot of new features have been introduced in the WhatsApp recently. You may know some of them or all of them. I am listing here 4 new features that are very handy.

1. Sending messages to multiple people - Message broadcasting

Earlier there was no option to send messages more than one person at a time, without creating a group. But now, WhatsApp has added this new feature where you can send a message to multiple persons. Remember, you cannot forward a message to multiple people. It has to be your own message and not the forwarded message. If you want to broadcast a forwarded message, then you can copy and send it to multiple persons

Follow these steps to broadcast a message to multiple persons.

On the home screen of WhatsApp, select CHATS, then click on 3 dots, which will display a menu.

You will find a menu item called New Broadcast, click on it. In the next screen, you will get an option to chose the contacts to which you want to broadcast a message. Select the contacts and click on the tick mark at the bottom to go the compose screen.

2. Change the way ENTER key works

In most cases when you press the ENTER key on your keyboard, it takes you to the new line. But in WhatsApp, you can configure it to act as a SEND button. That means, whenever you press the ENTER key, the message, which is already composed will be sent. You can configure this in the CHAT settings.




Under the CHATS menu, click on the 3 dots. Then select Chats

In the next screen, under Chat Settings if the box is checked, then ENTER will act as a send key. If you uncheck this box, then ENTER key will take you to the new line while composing a message. 

3. Check who has read or received your message in a WhatsApp group

If you are sending a message to a single contact then you can easily see whether the message has delivered or not. You can also see if the recipient has read your message or not, provided, the recipient has enabled the REAS RECEIPTS. 

What if you are sending a message to a group, you still have an option to check who has read your message and whom and all the message has delivered. 

Follow the below steps.

Select the message you have sent to a group by long pressing on it. 

Then click on the 3 dots at the top right corner, select Info.

You will see 2 sections, one Read By and second Delivered to. This shows who has read your message and to whom all the message has delivered.  

4. Change the FONT in the WhatsApp - More Options 

This feature was introduced a little while ago, check out more options below 

Parsing a text file and importing values to MS Excel

We can use Excel VBA script to analyze a text file for a specific purpose. In this example, I have a text file that contains a list of files and their details like name, date created, file size, etc. This is an extraction of DIR command in the dos prompt. I am trying to find a text string "CNT" in each line and if it is present then I will extract the name of the file and store that in the Excel Cell along with the date the is created. 

I have hardcoded the column here, you can modify it however you want. The first column will keep the name of the file and the second column will keep the file creation date. 

Sub import_text_file_to_excel()

Dim fileHandle As Integer

Dim fileLine As String

Dim r As Integer

Dim c As Integer

r = 1

c = 1

Open "C:\\Users\\krsanjee\\files.txt" For Input As #1

While Not EOF(1)

Line Input #1, myline

On Error Resume Next

line_first_part = Left(myline, 40)

line_second_part = Right(myline, Len(myline) - 40)

If InStr(line_second_part, "CNT") Then

arr = Split(line_first_part, " ")

ActiveSheet.Cells(r, c).Value = arr(0)

ActiveSheet.Cells(r, c + 1).Value = line_second_part

r = r + 1

End If

Wend

Close #1

End Sub

You should know these MS Excel skills if you are using MS excel

If you are using excel for whatever reason then knowing these skills will definitely increase your productivity. These are not advanced but moderate skills. You can manage with excel without knowing these skills but knowing these skills will improve the proficiency in Excel.

1. Defining named tables.

You can define a range as a table and give a name to it. Later you can use this name in a formula. You can also use these named tables in references.

Under formulas menu bar, select Define names. Then give a name to the tables, for example, Sales. Then select the range and select ok. After you create the named table, you can use the table name 'Sales' in the formula to calculate the sum.

2. Autofill 

Using autofill you can complete the series based on the current values. For example, if you want to fill multiple of a number, you can enter the first 2 numbers of the series and click on the autofill icon and drag down the column. In the newer version of the office, you will get multiple options. 

The options are

copy cells

This will repeat the already available items, instead of completing the series with the next numbers. 

Fill series

Completes the series with the next values. 

Fill format only

It will ignore the values. Just copies the format of the existing cells. It can alternate the colors based on the selection.

Fill without formatting

This will ignore the format but copies the value of the series. 

3. How to get to the new line within a cell

As you know Excel is a spreadsheet tool, the basic building block is a cell. Unlike any word processing tool, if you press 'Enter Key' it will take you to the next cell. What if you want to enter a new line within the cell. You can do that by holding 'ALT' key and pressing 'ENTER' key. 

4. Data bars

If you want to represent your data visually amazing, you can use data bars. It is very simple and it looks very attractive. The data needs to be numerical values. This is one kind of conditional formatting. You can create a bar horizontal graph just by applying a conditional format. 

Select the range you want to apply the format. Go to Conditional formatting and select Data Bars. Under the gradient fill, select whatever color you want. 

   

If you want the Data Bars in a separate column, then copy the values in the next column. Apply the conditional format on the new column but not on the old column. Edit the condition formula and check the box 'Show Only Bars'

5. Paste special 

Under the paste special you have options like transposing a the selected. That means you converted a row into column and column to row. 

You can paste only the value if the source is a formula

You can paste only the formula, not the values. 

You can also link destination cells to the source cell. By this, you can make sure that any change in the source will also get affected by the destination. 

6. Text to column


Suppose you have a bank statement in PDF format. If you bring the statement into excel you will have a lot of options to analyze it. One example would be a BRS, Bank Reconciliation Statement. Import the statement to excel and apply formulas on the imported statement.

You can split the single line into multiple columns based on a specific delimiter. In the below example list folders and files have been extracted to a text file and it contains columns like date and time, type of the file, and the name of the file and folder. Each of these columns can be split into separate columns in the excel. Once the data is in tabular form, it can be used for various analyses. In this case, you can sort the files based on their creation date or based on the names, etc.

After converting a single line into multi-column 

7. Auto-sizing a column width or row height. 

By clicking on the line between 2 rows or a column, you can auto adjust the width of the column or height of a row based on the content of that column or row.

8. Row and column selection shortcuts 

Ctrl + Space	To select the entire column
Shift + space	To select an entire row
Ctrl + Shift + Space:	The First click will select the filled range surrounded by the current cell. Second, the space press selects the entire worksheet.
Ctrl + shift + +	Insert a new row or a column
Ctrl + -	To delete a row or a column

  

How to swap 2 excel ranges | swap with multiple cells selected

I had created a macro to swap 2 cells values in MS Excel. By just clicking a button a macro could interchange the content of 2 excel cells.  There was a limitation on that macro. It could only work only if the selection is just 2 cells. It could interchange the value of just 2 cells but not the ranges. I used to get a lot of request from my viewers to extend this macro for more than 1 cell. Due to time constraint, I could not work on it.

Excel VBA macro to swap 2 values in cells

Now, I have written another macro which will work on the Excel ranges. This macro is completely different. My original macro was based on area selection. But the new macro is based on the ranges. It is very small and easy to understand, unlike another macro, which is comparatively bigger.

I will explain each of the lines in the macro.

Check out my youtube channel for more amazing excel and office automation videos

My youtube channel

1. Sub swap_2_ranges()

2. string_all_selected_area = (Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))


The first line is the subroutine name. The second line gets the string of value of all the ranges selected. The output will be "I23,H25,G22" format. It says 3 ranges are selected. Each range is separated by a comma. I will use this string later to get the ranges.

3. array_selected_area = Split(string_all_selected_area, ",")

The above line splits the multiple ranges into one range and stores them in an array

4. If (UBound(array_selected_area) = 1) Then
5. area1 = array_selected_area(0)

6. area2 = array_selected_area(1)

Then, there is an if condition to check if the selection is suitable for our swap operation. It will not work as expected if we select more than 2 ranges. or less than 2 ranges. Our purpose is to swap or interchange the content of 2 different ranges. So the selection should be exactly 2 ranges.

After this check, the selected ranges are stored in 2 temporary variables.

7. selection_1 = Range(area1)
8. selection_2 = Range(area2)

The variables are converted to Excel ranges. This is very important, as we are not getting selection directly in the Excel range format. Instead, the selection is in text format. The string values should be converted into an Excel range object so that we can apply some Excel VBA functionalities.

9. If Range(area1).Count = Range(area2).Count Then

Then comes one more if condition to check if the selected ranges have the same number of cells. It is obvious that we should have the same number of cells on each selection so that we can interchange their values.

10. Sheets(1).Range(area2) = selection_1
11. Sheets(1).Range(area1) = selection_2

This is actual swapping code. This is achieved through the temporary variables.

12. End If
13. End If

14. End Sub

Finally ending the if conditions and the Subroutine.

Practical Text Manipulation using MID LEFT RIGHT FIND Functions in MS Excel

Text manipulation is one of the great features of MS Excel. It has a lot of built-in functions to extract desired values from a text. By using different combinations of these functions we can create a complex formula.

I have listed a few practical examples which are very useful.

Extracting a Door number from an address text. 

8314 W. Galvin Lane West Des Moines, IA 50265

68 4th Ave. Neptune, NJ 07753

8 Oklahoma St. Ladson, SC 29456

I will use the LEFT and FIND functions to extract the Door number of an address. 

=LEFT(B4,FIND(" ",B4))

Here I am using the delimiter space (" "). The formula first finds a space character which appears after the door number. Then extracts the value whatever if at the left side of the first space. 

Extracting a ZIP code from an address 

Generally, the ZIP code will at the end of the address. Using the RIGHT function, we can extract the ZIP code 

=RIGHT(B9,5)

Extracting a specific number from anywhere in the text 

Suppose, from the below text I need to extract the dollar amount Which may be present anywhere in the text. For this, I will use multiple functions. For better understanding and for clear visibility,  I have separated the functions. If you understand them clearly then you can create your own complex formula. 

I had $100 in my pocket
bought 2 pens for $1 each
Spent $50 for snacks. 
Now I have $48 left

First, identify the starting position of the Dollar symbol.

=FIND("$",B10) Then identify the next

Then identify the next space after the Dollar symbol and the digits using the below functions

=FIND(" ",B10,FIND("$",B10))

Finally, extract the context between the first index, tha is a dollar sign and the space character after the amount. For this, I am using the mid function

=MID(B10,C10,D10-C10)

Extracting the names from a text 

The below data has names mixed with some other text. The left part does not have any unwanted text. So we don't have to worry about the left part. But the right portion has other text and we want to remove that. The text marked in red are to be removed. 

John Smit (IT Dept)
Krishh J (Sales)
Priore Priore (Software)

Lucy S (Sales)

It is very clear that we need to extract the text up to the first "(" symbol. This is can be done using a FIND and MID functions. Note the -1 at the end of the formula. This is to exclude the "(" character. Because the FIND function will return the position of the "(". But we don't need it. 

=LEFT(B17,FIND("(",B17)-1)

Practical examples on MS Excel date functions

MS Excel has an amazing set of Date and Time functions. By tweaking these functions, we can create a complex and useful formula. I am trying to list down a few practical examples particularly on the Date part, which will be very useful for a moderate to advanced Excel user. 

How to find the end of the month for a given date

Note that EOMONTH will return a serial number. You have to change the format to date format. 

=EOMONTH(TODAY(),0)

=EOMONTH(DATE(2018,10,10),0)

How to find the number of days remaining in a month

This is useful for interest calculation on first and last month on any type of loan repayment schedule. 

Use DAYS, EOMONTH, and DATE functions.

=DAYS(EOMONTH(TODAY(),0),DATE(2018,10,10))

Number of days remaining in this month or current month

=DAYS(EOMONTH(TODAY(),0),TODAY())

Find the number of months between two dates

In other words, find the date difference in terms of months. DATEDIF is one of the very useful formulas in MS Excel. It will take 2 parameters, the first parameter is the date and the second parameter is the unit. A unit may be Y, M, or D each constitutes Years Months and Days.

=DATEDIF(TODAY(), DATE(2019,1,10), "M")
=DATEDIF(TODAY(), DATE(2019,1,10), "Y")
=DATEDIF(TODAY(), DATE(2019,1,10), "D")

If you want to calculate the non-cumulative difference use these options.

"MD" - Ignores the Year and Month.
"YD"  - Ignores the Year
"YM" - Ignores the Year

How to construct a date using Day, Month and Year

DATE formula returns a date value. Takes 3 parameters - day, month, and year. This formula is useful whenever you want to construct a date from different formats or from a text value. Before using this formula to extract date value from a text, you need to parse and get the individual DAY, MONTH, and YEAR values. I will discuss more on this later

Syntax: 

=DATE(YEAR, MONTH, DAY)

=DATE(2018,10,10)

A practical scenario where the DATE function is useful. You have the date but you only want to change the Month. 

=DATE(YEAR(B2), 12, DAY(B2))

How to get the first and last day of a month

First day:

=DAY(EOMONTH(TODAY(),-1)+1) 

The above formula finds the end of the month of the previous month and adds one day to the date and then returns the day of the date value.

Last Day:

It is very simple to find the last day of a month using EOMONTH function

=DAY(EOMONTH(TODAY(),-1))

EOMONTH takes 2 parameters, Date and Month. The month value will be positive for future dates and negative for past dates, 0 for the current month.

0   - for the current month
-1  - previous month
3   - the 3rd month from now

How to convert month number to month name

Using the TEXT function, the month name can be extracted from a date value. Note the different formatting options and outputs. 

=TEXT(TODAY(),"mmmm")
=TEXT(TODAY(),"mm")
=TEXT(DATE(2018,1,1),"m")
=TEXT(DATE(2018,1,1),"mm")
=TEXT(DATE(2018,1,1),"mmm")
=TEXT(DATE(2018,1,1),"mmm")

How to get the numerical value of a month text

Suppose I have a month value in the string format, say 'October', and I want this in numerical equivalent value 10. There is no readily available excel formula to get the numerical value of this string. We can use the below trick to get the numerical month value

In excel there is a shorthand to enter a date. By entering 1oct and then by pressing the enter key, we can get the date 1st October of the current year. Using this technique I will construct a formula to get the numerical value of text month value

=MONTH(1&LEFT(B24,3)) =MONTH(1&B25) =MONTH(1&MID(B26,6,3))

How to get a Day, Month or a Year of a given date

It is very simple to extract a day, month, or a year from the date value. Just use the built-in Excel functions as shown below. These functions alone may not be that useful, but whenever you need to write a complex formula, these functions will be very helpful

=MONTH(TODAY())
=YEAR(TODAY())
=DAY(TODAY())

Convert any text into date format

If you have a date in a different format or the date is represented as text then is difficult to use that for any calculation purpose. First, you need to convert them into proper date format. You have to use different methods or techniques in different situations. I am giving a few examples here.

VBA Code to find a record in the MS word mail merge

MS Word mail merge has an option to navigate through the records. The below VBA code will help you to type and search the record within the MS word document area, you do not have to go to the menu bar.

This code first resets the document to the first record. This is required as the mail merge navigation is only forward. That means the VBA code cannot traverse backward.

Reads a text from TextBox which is placed within the document. If the keyword entered and the current record is matched, then the Active document is activated with the current record.


With ActiveDocument.MailMerge
 .DataSource.ActiveRecord = wdFirstRecord
End With

Dim numRecord As Integer
Dim searchText As String
searchText = TextBox1.Text

Set dsMain = ActiveDocument.MailMerge.DataSource
If dsMain.FindRecord(FindText:=searchText, Field:="Name") = True Then
numRecord = dsMain.ActiveRecord
End If
End Sub


Watch the complete step by step tutorial

Download the Excel file and the word document from this link below

Download