Text Functions - 'R' to ''S"

REPLACE   - replace part of string with particular string
-------------------------------------------------------------------------------
Syntax: REPLACE(old_text, start_num, num_chars, new_text)
 
Example: =REPLACE("old string",1,5,"eee") returns "eeetring"

Note: here start_pos =1 ,length = 5  => "old s"
          replace with eee => "eeetring"



REPT   -repeats a text given number of times
---------------------------------------------------
Syntax:  REPT(text, number_times)

Example: =REPT("Hi",4) returns "HiHiHiHi"


 RIGHT -  returns last 'n' number of characters from a string
----------------------------------------------------------------
Syntax: RIGHT(text,[num_chars])

Example: =RIGHT("Hello") returns "o"
               =RIGHT("Hello",2) returns "lo"

 Note: [num_chars] is optional , by default it is 1



SEARCH - Searches one text string within a second text string
 ----------------------------------------------------------------------
 Syntax: SEARCH ( find_text , within_text , [start_num] )

Example:  =SEARCH("man","superman returns") returns "6" as man starts at 6th position
               =SEARCH("he","he is a hero") returns 1
               =SEARCH("he","he is a hero",1) returns 1
               =SEARCH("he","he is a hero",2) returns "9"
              as startposition is 2 and "he" in "hero" is a match at 9th position

Note:      it returns "#Value" if the string is not found in this case we can use IFError Method

SUBSTITUTE THE LAST OCCURANCE OF A TEXT IN A STRING

Text               :  this is a isotype

Search Text  :  "is"

Replace with :  "@@"

Formula        : 
=SUBSTITUTE(A1,"is","@@",INT(LEN(A1)-LEN(SUBSTITUTE(A1,"is","")))/2)

Result             : this is a @@type

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• Text  Required. The text or the reference to a cell containing text for which you want to substitute characters.
• Old_text  Required. The text you want to replace.
• New_text  Required. The text you want to replace old_text with.
• Instance_num  Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Text Functions - 'L' to 'P'

LEFT- returns specified number of characters from left
-------------------------------------------------------------------
Ex. =LEFT("welcome",3) returns "wel"
     =LEFT("welcome") returns "w"


LEN  - returns length of string
-------------------------------------------------------------------
Ex. . =LEN("welcome") returns "7"


LOWER - returns string in lowercase
-------------------------------------------------------------------
Ex. . LOWER("HeLlo") returns "hello"


MID -returns string from middle of a string
------------------------------------------------------------------- 
Ex.  =MID("welcome",2,4) returns "elco"
       =MID("welcome",3,1) returns "l"

different tricks with mid function

NUMBERVALUE -returns the number value in locale

-------------------------------------------------------------------
Signature :  NUMBERVALUE(number , decimal_seperator , group_seperator)

Ex. . =NUMBERVALUE("2,550k88" ,  "k" ,  "," ) returns "2500.88"

     =NUMBERVALUE("2,550.88" ,  "." ,  "," ) returns "2500.88"

PROPER  - returns string in Proper case (first letter in Capitals rest in small)
-------------------------------------------------------------------

Ex. . =Proper("welCOMe") returns "Welcome"

check LOWER , UPPER

Text Functions - 'D' to 'F'

DOLLAR - returns currency format of a number
-----------------------------------------------------------------
Ex. =DOLLAR("10.625",2) returns 10.63
     =DOLLAR("10.624",2) returns 10.62
     =DOLLAR("10.625") returns 10.63
Note: second argument is 2 by default if not given


EXACT - matches two strings and returns true/false. it is case sensitive
-----------------------------------------------------------------
Ex. =EXACT("hello","hello") returns true
     =EXACT("hello","Hello") returns false


FIND - returns the position (>=1) of a string in another string
-----------------------------------------------------------------
Ex. =FIND("is","it is the fish season") returns 4
     =FIND("is","it is the fish season",5) returns 12
Note: 3 argument is the position from where it will start checking


trick to find second instance of a string


FIXED - Rounds a number to specified number of digits

-----------------------------------------------------------------

VALUE	Formula	Result	Description
1234	=FIXED(A2, 1)	1,234.0	Rounds the number in A2 one digit to the right of the decimal point.
1234	=FIXED(A3, -1)	1,230	Rounds the number in A3 one digit to the left of the decimal point.
1234	=FIXED(A4, -1,TRUE)	1230	Rounds the number in A4 one digit to the left of the decimal point, without commas (the TRUE argument).
1234	=FIXED(A5)	1,234.00	Rounds the number in A5 two digits to the left of the decimal point.

Text Functions - 'C'

CHAR- returns the character value of a number
--------------------------------------------------------
Ex.  =Char(65)  returns A
       =Char(123) returns {
Note:  user function CODE to return numeric value of a character

CLEAN - removes all non printable characters
--------------------------------------------------------
Ex:. =Clean(var) returns all printable characters in variable var

CODE- returns numeric value of a character (or first character in a string)
--------------------------------------------------------
Ex: =CODE("A") returns 65
     =CODE("AB") returns 65 (65 if code for A)

CONCATENATE  - joins two or more strings
--------------------------------------------------------
Ex:  =CONCATENATE("hello","boy") returns "helloboy"
       =CONCATENATE("it","is","a","nice","weather") returns "itisaniceweather"