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.