MID Function in Excel

The MID function returns the character from the middle of a text string where a staring position and length are mentioned. If you ever need to display some characters from a string, then the MID function is best for you. It is one of the popular functions of excel and widely used in a complex formula.

Syntax of the MID function is

MID(text,Start_num,num_chars)

Text: The text from where you want to extract characters.

Start_Num: It is also called the starting position. For example, If you want to extract "soft" from the string "Microsoft", here start_num will be 6. #VALUE error will come if this argument is zero.

Num_chars: This argument shows how long our result will be. In the above example, the num_chars is 4.

In the above example, you easily find that if the start_num and num_chars are zero the MID function gives a blank result.

Char Function in Excel

The Char function returns the character specified by the code number from the character set for your computer. The character set varies from the operating system to the operating system. The main purpose of char function is that get a character from the number.


Syntax of the Char function is

=CHAR(number)

In the example given above, you can see that every number (A number range from 1 to 255 )has been converted into a character with the help of char function. When we want to convert a number more than 255 its gives an #VALUE error.

Numbervalue function in Excel

Numbervalue is a function that converts a string into a number in a Locale-independent way.


Syntax of the Numbervalue function is

=Numbervalue(Text,[decimal_separator],[group_separator])

Arguments

Text: The string that you want to convert.

Decimal_Separator: It is optional. The character that separates the integer and decimal part from the number.

Group_Separator: It is optional. The character that separates the group from the number.

            

Value Function in Excel

The value function is a function that converts a text that represents a number to number. The value function will change the same text that has a numeric value in the cell.


Syntax of the Value Function

=VALUE(Text)

Here text arguments must be Number, Date, and Time format.

When you try to convert a non-numeric text it gives a #VALUE error.

Text Function in Excel

The Excel TEXT function is a function that converts a value to text in a specified format.

Syntax of the Text function is

=TEXT(value,format_text)

A number format is a special code to display the value in the desired format. It changes only its display without changing the original data. This is very useful for automating formatting.

There are many Build-in formats present in excel. I have used some build-in formats, you can use any of the formats.

Fixed Function in Excel

Fixed Function in excel is a function that rounds a number to the specified numbers of decimals and returns as text with or without commas.

Syntax of the Fixed function is

=FIXED(Number,[decimals],[no_commas])

Arguments

Number: The number you want to round, you can provide the number directly or reference of number.

Decimals: It is optional, After decimal how many digits you want to round the number, you can mentions here. By default, it is 2.

No_commas: True for preventing commas and False for not preventing commas. By default, it gives the result with commas.

Bahttext function in Excel

Bahttext is a function that converts a number to Thai Baht text with a suffix "Baht".

Syntax of Bahttext function is

=BAHTTEXT(number)

From two example it is very clear that "Bahttext" function only converts a number. If we enter a text then it gives an error "#VALUE". 

Upper Function in Excel

If you have written a message in lowercase letters and you want to change it to uppercase letters, then Upper function is for you. Upper function in excel converts the text in Uppercase.

Syntax of Upper Function is

=UPPER(Text)

 In the example, I have taken 4 text strings in the column and apply a function (=Upper(A1)) in Column B. All lowercase characters are converted to uppercase, Number and special characters are unaffected.

Proper function in Excel

Proper function  Converts all characters in a supplied text string to the proper case. A proper case means a letter that does not follow another letter is the upper case and all other characters are lower case.

Example
I am using MS excel 2016        -> Not a proper case
I Am Using Ms Excel 2016      -> A proper case


Syntax of Proper Function is

=PROPER(Text)

By using Proper finction you can capitalize each word of the string. The number and special characters are not affected. 

Lower Function in Excel

If you have written a message in uppercase letters and you want to change it to lowercase letters, then lower function for you. Lower function in excel converts the text in lowercase.

Syntax of Lower Function is

=LOWER(Text)

 In the example, I have taken 4 text strings in the column and apply a function (=Lower(A1)) in Column B. All uppercase characters are converted to lowercase, Number and special characters are unaffected.

Trim Function in Excel

Trim is a very useful Build-in function in excel. The trim function removes all spaces from a text string except for a single space between two words. The trim function removes all the extra spaces from the text string.

Syntax of the Trim Function is

TRIM(text)

In the above example, I have entered 4 types of data.

1 Space Place at the starting of the string.
2  Two spaces between the words.
3 Space place at the end of the string.
4 Many spaces between two words.

I used the =TRIM(A1) function in Column B and you can see that the result of all the four cells is the same.
The Trim function removes all extra space from anywhere of the string.

Clean Function in Excel

Clean function in excel removes all non-printable characters from the text. This function is designed to remove all non-printable characters.

Syntax of the Clean function

CLEAN(text)

The ASCII code of the non-printable characters is 0 to 31. The following are the characters which are non-printable.

Named Ranges in Excel

Hello Friends,

Today I am going to share with you about a topic that many people avoid using it. Named ranges are one of these old features in Excel that few users understand. New users may find very difficult to understand and even old hands may avoid them because they seem pointless and complex.

Actually, it is very beautiful features of excel and easy to understand. It is a tool that helps to create your formula very shorter and reusable.

What is Named Range?

If you are asked to go to that place by giving graphical position details instead of the name of a location, then you may never reach. The name matters a lot in our life, in the same way, that Excel has given the option to name the range.

Named Range is the human-readable name of the range of cells in excel. This name can be widely used to create the formula.

Creating a Named Range

To create a Named range of range of data, you just select the range of cells you want to add in the Named range, type the name in the Name box. In my example, I select the range of cells (A1: A7) and type a name as "Numset".

You can create a named range by using the option "Define Name" in the Formula tab.

Type the name and select the range in the "Refers to:" and click on ok. Named Range created.

Update the Named Ranges

 To update like Edit/Remove the existing Named Range go to formula tab click on "Name Manager"

Now you can select the Named ranges by clicking on it, you can edit or remove the select named ranges.

Names have rules

When creating named ranges, follow these rules:

Names must begin with a letter, an underscore (_), or a backslash (\)
Names can't contain spaces and most punctuation characters.
Names can't conflict with cell references – you can't name a range "A1" or "Z100".
Single letters are OK for names ("a", "b", "c", etc.), but the letters "r" and "c" are reserved.
Names are not case-sensitive – "home", "HOME", and "HoMe" are all the same to Excel.


Named Constant

This is another good feature of the excel that we can name a constant value. If you want to use a constant value in a different worksheet of the workbook, then you can name that constant value and use it in every sheet.
To create a Named constant click on "define name", type the name and put the constant value in "Refers to".

Click OK, Named constant created. you can see all your named ranges and constant by clicking Name Manager or using shortcut key Ctl+F3.

Add Drop-down in Excel

Hello  Friends

A drop-down list also known as a drop-down menu, drop menu, pull-down list, picklist is similar to a list box, that allows the user to choose one value from a list. These features provide us with a special way by which we can select the data of anyone element from the list box in a cell.

In this topic, I want to share with you how we can add a drop-down menu in the cell. It is very easy to create a drop-down list in Excel and once you understand these tricks, it will help you create a dashboard in excel.

Follow the steps to add a drop-down

1.Create/Enter a list. I have taken the list of the month in my example.
2. Select the cell where you want to enter the drop-down.
3. Click the " Data Validation" from the Data tab.

4. Data validate dialog box appears, select the list from the "Allow" drop-down menu. Choose the In-cell dropdown checkbox. Now enter the references of your data and click on OK.

5. Select the cell, now you can see the drop-down. You can select any one the data from the drop-down list.

You always have to change the reference in the source if you want to add any data to your list. If you want your drop-down list to be automatically updated if you add any data to your list. To learn the trick to create it follow the link Create Dynamic Drop-Down.

Summation of every 3 (Quarterly) values of cells.

Hello Friends,

In this topic, I will tell you the trick/formula to the sum of every nth row and column of a range in excel.

If you know about the OFFSET function will easily understand this example. If you want to learn about the offset function, then follow the offset function.

In the above example, I have taken a monthly sales record of the items of a computer store. Now I want to make a report to find the quarterly sales of each item. To get the result I have used a formula.

=SUM(OFFSET($C6,0,(COLUMN()-COLUMN($P$6))*3, 1,3))

As we know, to calculate the first quarter, we have to add the first three months of sales. The sales data for the first three months is in the same row and 3 different columns. So in formula row is 0.

To add the value of 3 different months, mention the height (1) and Width (3). By changing the value of height and width you can add as many numbers of rows/columns as you want.

Similarly, you can get the result if your monthly data in rows wise by using the formula

=SUM(OFFSET(C$3,(ROW()-ROW($H$3))*3,0,3,1))

Offset Function in Excel

Hello Friends,

The "Offset" function in excel is a powerful function. The Offset function returns a reference to a range that is given a number of rows and columns from a given reference. It is a function that returns the value of cell or range of cells which is specified by a number of rows and columns from the cell and range of cells.

Syntax of the Offset Function.

=OFFSET (reference, rows, cols, [height], [width])

Reference: Reference of cell or range of cells from where a row and column count will start.

Rows: Count the number of rows below (a positive value), above (a negative value), same row (0).

Column: Count the number of column right (a positive value), left (a negative value).

Height: It is optional. The height in rows of the returned reference.

Width: It is optional. The Width in a column of the returned reference.


Example of the OFFSET function to understand it.

In this example, I have taken monthly sales of 3 items of a computer store. Now I want to calculate the sum of the sales of all three items in the month of July.

I used a formula

=SUM(OFFSET(A3,6,1),OFFSET(A3,6,2),OFFSET(A3,6,3))

In the above formula, I have used the OFFSET function 3 times for the references of three different items.
A3 is the starting point from where we can calculate the number of rows and columns. The reference of the "January"  value of  A3 is 0 row. and 0 columns.

OFFSET(A3,6,1) returns the reference of the cell which is located at 6 rows below and 1 column right from the cell A3.

It is a very powerful function and widely used in a complex function. Here some more examples of OFFSET function.


Sum Of every 3 cells

Use Of Wildcards in Excel

Hello Friends,

In this tutorial, I will give you examples of wildcards character in Excel. Wildcards are very useful in excel.

There are only 3 Excel wildcard characters (asterisk, question mark, and tilde) and a lot can be done using these.

1. Asterisk (*): It represents any number of characters. It means "*" character represents one or more than one character. Example "IN*" could mean  INDIA, INDONESIA, INOVA.

2. Question Mark (?): It represents only one character. Example  "IN?" could mean INX, IND, INT.

3.Tilde (~): It is used to find the wildcard character (Asterik, Question Mark) in text. Example Suppose you want to find the word followed by *, you have to write the tilde character before the asterisk (~*text).

Examples of  Asterisk:

Count the text from the data range that ends with "ard".

Count the text from the data range that starts with "mo".

Examples of  Question Mark:

Count the number of text from the data range that has only 3 characters and starts with "MO".

Count the number of text from the data range that has 3 characters long.

Examples of  Tilde:

Count the number of text that has "*" character.

Finding Text in Multiple Columns

Dear Friends,

Sometimes we have to search for text in multiple columns. In this topic, we will learn to search for text and if you get the text, then display "Yes" in the cell or else display "No".To get this result, I have two Excel functions which are IF() and Countif().

IF(): Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE.

COUNTIF(): The Excel Countif function returns the number of cells within a supplied range, that satisfy given criteria.

In my example, I have entered different sentences from column A1 to E1.

Now I want to search a text "Gardner" in column A1 to E1. The formula I am using is

=IF(COUNTIF(A1:E1,"*Gardner")>0,"Yes","No")

Here I am using a wild card "*" to find the result that is less than exact. To know more about wildcards follow the topic use of wildcards in excel.

You can get the result by editing as per your data set range and text.

Reverse Order of Columns and Rows in Excel

Hello Friends,

We often get such a situation when we have to reverse the content of the columns or rows. I have created a formula for it that can save your lot of copy-paste time.

I have entered the name of the day in column A (A1 to A7, the entry must be non-blank) in my example. Put the following formula in any cell and drag down as far as you need (you'll get a zero when there are no more entries).

=(INDEX(A:A,COUNTA(A:A)-ROW(A1)+1)

Similarly, we can reverse the ROW data by using the following formula (data enter in row 1)

=INDEX(1:1,COUNTA(1:1)-COLUMN(A1)+1)

You can edit the formula by changing the column and row number. Some Screenshots are posted below.

Conditional Formatting Example with Formula

Dear Friends,

Today I have introduced a great example of Conditional Formatting.

In my example, I have taken two Column, one of which has a date and the availability of items in the 2nd column. I want to search and format the date row and availability row that comes just after today's date and its availability is 0.

1. Open Microsoft Excel.
2. The header in Column A is Date and column B is Availability and fill the data

3. Enter Current Date in E2.
4. Now, Select the data A2 to B18, Click the Conditional Formatting in HOME TAB.
     Click on New Rule.

New Formatting Rule dialogue box will appear, click "Use a formula to determine which cells to format" and Paste the formula         

          =AND($A2=MIN(IF($A$2:$A$18>=$E$2,IF($B$2:$B$18=0,$A$2:$A$18))),$A2<>"")

       in the "Format values where this formula is true" box
.

5. Click on Format button. Click on fill tab and select the color and click on OK.

Here is result output Screen (I have chosen blue color).

You can change the Current date manually and formatting will change automatically.







                           

Reverse The String In Excel

Hello Friends,

If you want to display a string (cell string) in a reverse way, here I have a trick for you.

VBA code for reverse a string.

1. Open the Microsoft Visual Basic Application.
2. Click Insert>Module.
3. Write the following Code.

Writing this code save and close the Microsoft Visual Basic Window.

Now, You can call this "ReverseString" function in any cell on same worksheet. Some Screenshot is for your references.

Remove Any Number of Characters From String In Excel

If you want to remove any number of characters from string in excel (Cell String) at a time, here I have a trick for you.

To get the desired result you have to write VBA code.

1. Open the Microsoft Visual Basic Application.
2. Click Insert>Module.
3. Write the following Code.

             Function RemoveSpecial(str As String) As String

                      Dim spchar As String

                      Dim i As Integer

                      spchar = "/* -#$9"

                      For i = 1 To Len(spchar)

                      str = Replace(str, Mid(spchar, i, 1), "")

                      Next

                      RemoveSpecial = str

             End Function

Save and close the Microsoft Visual Basic Application Window.

In the above Code only (/,*,-,#,$,9) are removed from the string. You can add or delete character from the statement   "spchar = "/* -#$9" " as per your requirement.

Now, You can call this "RemoveSpecial" function in any cell on same worksheet.