Skip to main content

EXCEL FORMULA PART -5


                           TEXT FUNCTION































  1.CONCATENATE FUNCTION :





Summary 
The Excel CONCATENATE function concatenates (joins) join up to 30 text items together and returns the result as text. The CONCAT function replaces CONCATENATE in newer versions of Excel.
Purpose 
Join text together
Return value 
Text joined together.
Syntax 
=CONCATENATE (text1, text2, [text3], ...)
Arguments 
  • text1 - The first text value to join together.
  • text2 - The seond text value to join together.
  • text3 - [optional] The third text value to join together.
Usage notes 
  • CONCATENATE can join up to 30 text items together.
  • Text items can be text strings, numbers, or cell references that refer to one cell.
  • Numbers are converted to text when joined. If you need to specify a number format for a number being joined, see the TEXT function.
  • The ampersand character (&) is an alternative to CONCATENATE. The result is the same, but the ampersand is more flexible, and creates formulas that are shorter and (arguably) easier to read.




2. LEFT FUNCTION :-




Summary 
The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".
Purpose 
Extract text from the left of a string
Return value 
One or more characters.
Syntax 
=LEFT (text, [num_chars])
Arguments 
  • text - The text from which to extract characters.
  • num_chars - [optional] The number of characters to extract, starting on the left side of text. Default = 1.
Usage notes 
  • Use the LEFT function when you want to extract characters starting at the left side of text.
  • num_chars is optional and defaults to 1.
  • LEFT will extract digits from numbers as well.
  • Number formatting (i.e. the currency symbol $) is not part of a number so is not counted or extracted.

 3.RIGHT FUNCTION





Summary 
The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT("apple",3) returns "ple".
Purpose 
Extract text from the right of a string
Return value 
One or more characters.
Syntax 
=RIGHT (text, [num_chars])
Arguments 
  • text - The text from which to extract characters on the right.
  • num_chars - [optional] The number of characters to extract, starting on the right. Optional, default = 1.
Usage notes 
  • Use the RIGHT function when you want to extract characters starting at the right side of text.
  • num_chars is optional and defaults to 1.
  • RIGHT will extract digits from numbers as well.
  • Number formatting is not part of a and will not be extracted or counted.




4.DOLLAR FUNCTION



Summary 
The Excel DOLLAR function converts a number to text using the Currency number format. The TEXT function can do the same thing, and is much more versatile.
Purpose 
Convert a number to text in currency format
Return value 
A number as text in currency format.
Syntax 
=DOLLAR (number, decimals)
Arguments 
  • number - The number to convert.
  • decimals - The number of digits to the right of the decimal point. Default is 2.
Usage notes 
  • The DOLLAR function converts a number to text using currency number format: $#,##0.00_);($#,##0.00).
  • The default for decimals is 2. If decimals is negative, number will be rounded to the left of the decimal point.
  • The name of the function and the currency symbol used is based on language settings of the computer.
  • The TEXT function is a more flexible way to achieve the same result.


5. LOWER FUNCTION 


Summary 
The Excel LOWER function returns a lower-case version of a given text string. Numbers and punctuation are not affected.
Purpose 
Convert text to lower case
Return value 
Text in lower case.
Syntax 
=LOWER (text)
Arguments 
  • text - The text that should be converted to lower case.
Usage notes 
  • All letters in text are converted to lower case.
  • Numbers and punctuation characters are not affected.

 6.UPPER FUNCTION 


Summary 
The Excel UPPER function returns a upper-case version of a given text string. Numbers and punctuation are not affected.
Purpose 
Convert text to upper case
Return value 
Uppercase text.
Syntax 
=UPPER (text)
Arguments 
  • text - The text thatto convert to upper case.
Usage notes 
  • All letters in text are converted to upper case.
  • Numbers and punctuation characters are not affected.

7.PROPER FUNCTION

Summary 
The Excel PROPER function capitalizes words given text string. Numbers and punctuation are not affected.
Purpose 
Capitalize the first letter in each word
Return value 
Text in proper case.
Syntax 
=PROPER (text)
Arguments 
  • text - The text that should be converted to proper case.
Usage notes 
  • Use PROPER to capitalize each word in a given string.
  • All letters in text will be converted to lower case before the first letter in each word is capitalized.
  • Numbers and punctuation characters are not affected.





8.REPLACE FUNCTION 


Summary 
The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE("XYZ123",4,3,"456") returns "XYZ456".
Purpose 
Replace text based on location
Return value 
The altered text.
Syntax 
=REPLACE (old_text, start_num, num_chars, new_text)
Arguments 
  • old_text - The text to replace.
  • start_num - The starting location in the text to search.
  • num_chars - The number of characters to replace.
  • new_text - The text to replace old_text with.
Usage notes 
The REPLACE function is useful for replacing text at a known location in a given string. For example, the following formula replaces 4 characters starting at the 3rd character:


=REPLACE("XYZ123",4,3,"456") // returns "XYZ456"
You can use REPLACE to remove text by specifying an empty string ("") as new text. In the formula below, we use REPLACE to remove the first character:


=REPLACE("XYZ",1,1,"") // returns "YZ"
Excel has several functions that can be used to replace text. Below is a brief summary. Also see linked examples below.
  • Use the REPLACE function when you want to replace text based on a known location.
  • Use FIND or SEARCH to find and replace text when the location is not known in advance.
  • Use SUBSTITUTE to replace one or more instances of text based on content only.

 9. MID FUNCTION 


























Summary 
The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".
Purpose 
Extract text from inside a string
Return value 
The characters extracted.
Syntax 
=MID (text, start_num, num_chars)
Arguments 
  • text - The text to extract from.
  • start_num - The location of the first character to extract.
  • num_chars - The number of characters to extract.
Usage notes 
  • MID returns a specific number of characters from a text string, starting at start_num and continuing through start_num + num_chars.
  • Use the MID function when you want to extract text from inside a text string, based on location and length.
  • You can use FIND or SEARCH to locate start_num when you don't know the location in advance.


10.LEN FUNTION




Summary 
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
Purpose 
Get the length of text.
Return value 
Number of characters
Syntax 
=LEN (text)
Arguments 
  • text - The text for which to calculate length.
Usage notes 
  • LEN is a useful when you want to count how many characters there are in some text.
  • Numbers and dates will also return a length.
  • Number formatting is not included. (i.e. the length of "100" formatted as "$100.00" is still 3).

Labels:
Location: 1032-33,11TH FLOOR ,West End Mall, Janakpuri West, Janakpuri District Center, Janakpuri, Delhi, 110058, India

Comments

Post a Comment

Popular posts from this blog

WHAT IS A COMPUTER? Hardware & Software

In this blog we will discuss about ,"What is computer and differences among its part as Hardware & Software". We know that all information are scattered in google but I want to make it easy for our readers.Otherwise it is difficult to know which information is right and which is wrong . My Blog is dedicated to learners & easy learning  WHAT IS A COMPUTER? What is Computer  : Computer is an electronic device that is designed to work with Information. The term computer is derived from the Latin term  ‘compute’ , this means to  calculate  or  programmable machine .   Therefore computers can perform complex and repetitive procedures quickly, precisely and reliably. Modern computers are electronic and digital. All general-purpose computers require the following hardware components: §   Central processing unit (CPU) : The heart of the computer, this is the component that actually executes instruction...
2 comments
Read more

FORMULA MS-EXCEL PART-4

                                DATE & TIME  FUNCTION   1. TODAY ( ) :- Summary  The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current date and time, use the  NOW function . Purpose  Get the current date Return value  Valid Excel date Syntax  =TODAY () Arguments  Usage notes  The TODAY function takes no arguments, and returns the current date, updated whenever a worksheet is changed or opened. You can also use F9 to force the worksheet to recalculate and update the value. If you need a static date that won't change, you can  enter the current date using the keyboard shortcut  Ctrl +...
Post a Comment
Read more

BRIEF OF EXCEL FORMULAS (PART-1)

EBEES INFOTRAIN CONSULTANCY SERVICES PVT. LTD. 1. SUM :  Summary  The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments. Purpose  Add numbers together Return value The sum of values supplied. Syntax  =SUM (number1, [number2], [number3], ...) Examples In the example shown, the formula in D12 is: = SUM ( D6:D10 ) // returns 9.05 2.Round() :  Summary  The Excel ROUND function returns a number rounded to a given number of digits. The ROUND function can round to the right or left of the decimal point. Purpose  Round a number to a given number of digits Return value  A rounded number. Syntax  =ROUND (number, num_digits) Arguments  number  - The number to round. num_digits  ...
Post a Comment
Read more