Excel TIPS - Function CONCATENATE

CONCATENATE

 CONCATENATE joins two or more text strings into one text string.

=CONCATENATE(text1,text2,text3,. . . . .)

Examples:

	A	B
1	110001	1259535
2	ROHIT	KUMAR

                    FORMULA                                             RESULT

=CONCATENATE(A1,B1)                                 1100011259535

=CONCATENATE(A2,B2)                                 ROHITKUMAR

=CONCATENATE(A2,”  “,B2)                           ROHIT  KUMAR                      

** Put “ ‘’ between text strings for space **

EXCEL TIPS - Functions LOWER, UPPER & PROPER

Excel TIPS on LOWER, UPPER & PROPER Functions.

FUNCTIONS LOWER, UPPER & PROPER

To change a text in Lower, Upper or Proper case, these functions are useful.

1.  LOWER

        

Converts all uppercase letters in a text string to lowercase.

=LOWER(text)

                                                                                                

Text is the text you want to convert to lower case. LOWER does not change characters that are not text.

Examples:

=LOWER(JOSEF)                  josef

=LOWER(jOSef)                   josef

2.  UPPER

Converts all letters in a text string to uppercase.

=UPPER(text)

Text is the text you want to convert to upper case. UPPER does not change characters that are not text.

Examples:

=UPPER(josef)                      JOSEF

=UPPER(Josef)                     JOSEF

3.  PROPER

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

=PROPER(text)

Text is the text you want to convert to proper case. PROPER does not change characters that are not text.

Examples:

=PROPER(this is a TITLE)               This is a Title

=PROPER(JOSEF)                                   Josef 

MS Excel TIPS - Excel Function ROUND

Function ROUND

Round function is very important and one of the most commonly used excels functions.

Round is used to round the value to a specified number of digits.

=ROUND (number,num_digits)

Here number is value which is to be rounded.

num_digits is number of digits up to which values is to be rounded.

Remarks:

·       If num_digits is 0, then number is rounded to the nearest integer.

·       If num_digits is greater than 0, then number is rounded to the specified number of decimal places e.g. if num_digits is 1, then number will be rounded to 1 decimal place. If num_digits is 2, then number will be rounded to 2 decimal places.

·       If num_digits is less than 0, then number is rounded to the left of the decimal point e.g. if num_digits is  -1 then number will be rounded to nearest 10. If num_digits is -2, then number will be rounded to nearest 100 and so on.

Examples:

FUNCTION	RESULT	REMAKRS
=ROUND(219.4,0)	219	Rounds 219.4 to nearest integer
=ROUND(219.6,0)	220	Rounds 219.6 to nearest integer
=ROUND(219.68,1)	219.7	Rounds 219.68 to 1 decimal place
=ROUND(219.649,2)	219.65	Rounds 219.649 to 2 decimal places
=ROUND(219,-1)	220	Rounds 219 to left decimal point
=ROUND(264, -1)	260	Rounds 214 to left decimal point
=ROUND(219, -2)	200	Rounds 219 to left decimal point
=ROUND(264, -2)	300	Rounds 264 to left decimal points

ROUND function can be helpful when multiplication formula is used in excel.

Example:

9563*4%   =      382.52

Here, ROUND function can be used. Function will be

=ROUND(382.52,0)              383

OR

=ROUND(9563*4%,0)          383