SUBSTITUTE()

SUBSTITUTE() - This function substitutes new text for old text in a string.

Syntax

SUBSTITUTE(text; search_text; new text; occurrence)

text is the text in which text segments are to be exchanged.

search_text is the text segment that is to be replaced (a number of times).

new text is the text that is to replace the text segment.

occurrence (optional) indicates which occurrence of the search text is to be replaced. If this parameter is missing the search text is replaced throughout.

Example:

=SUBSTITUTE("321032103210";"0";"Hello") returns 321Hello321Hello321Hello

=SUBSTITUTE("321032103210";"0";"Hello";2) returns 3210321Hello3210

FIND()

FIND() -This functions looks for a string of text within another string. You can define where to begin the search. The search term can be a number or any string of characters. The search is case-sensitive.

Syntax

FIND(find_text; text; position)

find_text refers to the text to be found.

text is the text where the search takes place.

position (optional) is the position in the text from which the search starts.

Example:

=FIND("f";"abcdefghijk") returns 6

=FIND("f";"a b c d e f g h i j k") returns 11

=FIND(81;3958362387817) returns 14

LEN()

LEN() - This function returns the length of a string including spaces.

Syntax

LEN(text)

text is the text whose length is to be determined.

Example:

=LEN("JimBob") returns 6

=LEN(Jim Bob") return 7

RIGHT()

RIGHT() - This function returns the last character or characters of a text.

Syntax

RIGHT(text; number)

text is the text of which the right part is to be determined.

number (optional) is the number of characters from the right part of the text.

Example:

=RIGHT("Southbound",5) returns bound

=RIGHT("Southbound") return d

LEFT()

LEFT() - This function returns the first character or characters of a text.

Syntax

LEFT(text; number)

text is the text where the initial partial words are to be determined.

Number (optional) specifies the number of characters for the start text. If this parameter is not defined, one character is returned.

Example:

=LEFT("Southbound";5) returns South

=LEFT("Southbound") returns S

CONCATENATE() or &

CONCATENATE() - This function combines several text strings into one string.

Syntax

CONCATENATE(Text 1;...;Text 30)

Text 1; text 2; ... represent up to 30 text passages which are to be combined into one string.

Example: Look at the screen shot below.

Column A contains the last name of a person and column B contains the first name of person. I want to combine these into one cell.

Cell C2 contains the formula =CONCATENATE(B2;A2) which returns TreyCook
Notice the above result does not put a space between the first and last names.

Cell D2 contains the formula =CONCATENATE(B2;" ";A2) which returns Trey Cook
This result put a space between the first and last name. Notice the difference in the formulas. The second one has " ". Note the double quotes has a space between them.

Another approach to combine several text strings into on string is to use "&" without the quotes.

Cell E2 contains the formula =B2&A2 which returns TreyCook
You can see this gives the same result as in cell C2.

Cell F2 contains the formula =B2&" "&A2 which returns Trey Cook
This gives the same result as in cell D2.

Screen Shot

CHAR()

CHAR() - This functions converts a number into a character according to the current code table.

Syntax

CHAR(number)

number is a number between 1 and 255 representing the code value for the character.

Example:

=CHAR(65) would return A

Here is a screen shot of all of the codes. You can download this workbook. Click on "Spreadsheet Function Files" under "Links" on the right hand side of the page.

ASC

ASC - This function converts full-width to half-width ASCII and katakana characters. It returns a text string.

For a detailed explanation see this link.

ARABIC

ARABIC - This function calculates the value of a Roman number.

Syntax:

ARABIC(text)

text - represents the text for the roman number. The value must be between 0 and 3999.

Example:

Ever see those Roman numbers at the end of T.V. shows. Well now you can put them into a spreadsheet and see what number they represent.

=ARABIC("MMVIII") will return 2008
=ARABIC("MCMLXX") will return 1970 (year I was born)

Note: The Roman numeral must be in "".

Screen Shot: