Text Functions (II)

FIND(find_text;within_text;start_num)

It searchs for a text inside another text and returns the position of the text searched. It performs the search reading the text from left to right, from the initial position indicated in numb_initial. In contrast with the function SEARCH, FIND does not distinguish between capital and small letters and admits joker characters (? just one character, * any number of characters, scape character).

Example: =FIND("Sea";"Watching the sea, an intense emotion takes hold of me...";1) returns 12

=FIND("W?r";"Watching the sea, an intense emotion takes hold of me...";1) returns 1

EXACT(tex1;tex2) Function

It returns a logical value (true/false) depending on whether both text chains are the same or not.

Example: =EXACT(" this is the same?";" is this the same?") returns FALSE

It is normally used to compare the values stored in two cells.

LEFT(tex;num_chars) Function

It returns the number of characters specified from the beginning of the text chain.

Example: =LEFT("The sun can not compete with the brightness of your gaze";6) returns "The sun"

 

LEN(text) Function

It returns the number of characters that the text chain has, that is its length.

Example: =LEN("The sun can not compete with the brighteness of your gaze") returns 51

 

CLEAN(text) Function

It gets rid of character that can not be printed.

Example:If you type in a cell:=CHARACTER(7)&"This text is valid"&CHARACTER(7) in the cell you would see this: if you type =CLEAN(CHARACTER(7)&"This text is valid"&CHARACTER(7)), the characters that can not be printed will disappear and you will see:

 

UPPER(text) Function

Converts into capital letters a text chain.

Example: =UPPER("converts to capital") returns "CONVERTS TO CAPITAL"

 

LOWER(text) Function

Converts to small letters a text chain.

Example: =LOWER("COME ON NOW SMALL LETTERS") returns "come on now small letters"

 

DOLLAR(number;decimals)

It converts to text a number using currency format.

Example: =DOLLAR(25;2) returns "25,00 € "

 

PROPER(text) Function

It converts the first letter of each word of a text into capital letters, the rest of the word into small letters.

Example: =PROPER("antonio manuel ramiro") returns "Antonio Manuel Ramiro"

 

REPLACE(old_text;start_num;num_chars;new_text) Function

It replaces part of a text chain for another.

Example: =REPLACE("If this is the original text, it will be modified";21;8;"Into this") returns "If this is the text Into this, will be modified"

 

REPT(text;num_times)

It repeats a text a previuosly fixed number of times.

Example: =REPT("You repeat yourself";5) returns "You repeat yourself You repeat yourself You repeat yourself You repeat yourself You repeat yourself"

 

SUBSTITUTE(text;old_text;new_text;instance_num) function

Substitutes in the text, an original text_ for new text_.

Example: =SUBSTITUTE("The price for the whole project implies..."; "price"; "cost") returns "The cost for the whole project implies..."

 

T(value) function

It checks whether the value is text and returns text if it is or double inverted comas if it isn't. With this function you can eliminate the values in a cell that aren't text.

Example: =T("This is text") returns "This is text"

 

TEXT(value;format_text) function

It converts a value in text.

Example: =TEXT(25;"0,00 €") returns "25,00 €"

 

BAHTTEXT(number)

Converts a number in thai (Baht) text.

The baht format can be changed into a different style used. Regional configuration or Regional options in the Windows Control panel .

Example: =BAHTTEXT(25) returns figure 25 in letter but in Thai.

 

VALUE(text) function

Converts a text that represents a figure in a figure.

Example: =VALUE("254") returns 254 numerical format.

 

 

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.