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

DIFFERENT OPERATORS IN MS-EXCEL

                                    OPERATORS   Operators are used in formulas to execute operations on the values taken by formulas.  Excel use 4 types of operators : 1.Arithmetic Operators 2.Text Concatenation Operators  3.Comparison Operators  4.Reference Operators  www.ebeescorp.com Arithmetic Operators This operation will explained through example Example  Text Concatenation Operators   This operation will explained through example Example  Comparison Operators  To compare values you can create formulas using comparison operators. These operators return a TRUE or FALSE result depending how the formula evaluate the condition .Some common operator are as follow: Reference Operators  Reference operators enable you to combine absolute and relative references and na...
Post a Comment
Read more

COMPUTER ABBREVIATIONS & SOME INVENTORS

ABBREVIATIONS EBEESCORP www.ebeescorp.com Inventors Of Computer Hardware  (1) Key board — Herman Hollerith, first keypunch device in 1930 (2) Transistor — John Bardeen, Walter Brattain & Wiliam Shockley (1947 - 1948) (3) RAM —        An Wang and Jay Forrester (1951) (4) Trackball — Tom Cranston and Fred Longstaff (1952) (5) Hard Disk — IBM , The IBM Model 350 Disk File (1956 ) (6) Integrated Circuit — Jack Kilby & Robert Noyce (1958) (7) Computer Mouse — Douglas Engelbart (1964) (8) Laser printer — Gary Stark weather at XEROX in (1969) (9) Floppy Disk — Alan Shugart & IBM( 1970) (10) Microprocessor — Faggin, Hoff&Mazor – Intel 4004 COMPUTER TRAINING CENTER 9717717164
Post a Comment
Read more

CHARACTERISTICS OF A COMPUTER & CENTRAL PROCESSING UNIT (C.P.U)

Hello, Readers This Blog will help you understand the Characteristics of Computer & About C.P.U (Central Processing Unit ). Characteristic means Computer capabilities i.e Characteristics of Computer  1.Speed 2.Accuracy 3.Consistency 4. Data Storing Capability 5.Flexibility  A. Speed : A Computer is a very fast device .It can perform , in a few Second ,the amount of work that a human being can do in an entire year working round the clock .The speed of the computer is usually 2 to 4 GHz . A powerful computer is capable of performing  about 10 to 40 million arithmetic calculations in a second. or We can say that Computer speed is measured in   megahertz (MHz) or gigahertz (GHz). One Megahertz equals to one million ticks per second,one Gigahertz equals one billion ticks per second . B. Accuracy : The accuracy of a Computer is consistently high an the degree o accuracy of a particular task is 100% accurate.In  a every computer each and e...
Post a Comment
Read more