• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 15. Text Functions > Text Functions Overview

Text Functions Overview

Text functions can be used in several ways. They can return the number of characters in text strings, remove extra spaces and nonprintable characters from cells, return exact data within a string, change the case of text strings, and even combine text from other cells. If you inherit workbooks from other people, you will eventually have to clean up or manipulate the data. Text functions allow you to create consistency throughout the workbook. Because certain functions are case sensitive, it's good practice to create consistency throughout lists and tables. This chapter covers the functions in Table 15.1 that are marked in bold.

Table 15.1. Text Functions
Function Syntax Description
CHAR =CHAR(number) Returns the character specified by a number.
CLEAN =CLEAN(text) Removes all nonprintable characters from text.
CODE =CODE(text) Returns a numeric code from the first character in a text string.
CONCATENATE =CONCATENATE (text1,text2,…) Joins several text strings into one text string.
DOLLAR =DOLLAR(number,decimals) Converts a number to text using Currency format, with the decimals rounded to the specified place.
EXACT =EXACT(text1,text2) Compares two text strings and returns TRUE if they're exactly the same, and FALSE otherwise.
FIND =FIND(find_text, within_text,start_num) Finds one text string with another text string, and starting position offind_text, from the leftmost character of within_text.
FIXED =FIXED(number,decimals, no_commas) Rounds a number to a specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
LEFT =LEFT(text,num_char) Returns the first character or characters in a text string based on the number of characters you specify.
LEN =LEN(text) Returns the number of characters in a text string.
LOWER =LOWER(text) Converts all uppercase letters in a text string to lowercase.
MID =MID(text, start_num,num_char) Returns a specific number of characters from a text string, starting at the position you specify.
PROPER =PROPER(text) Capitalizes the first letter of each word in a text string and any other letters in text that follow any character other than a letter.
REPLACE =REPLACE(old_text, start_num,num_chars, new_text) Replaces a portion of a text string with a different text string based on the number of characters you specify.
REPLACEB =REPLACEB(old_text, start_num,num_bytes, new_text) Replaces part of a text string with a different text string based on the number of bytes you specify.
REPT =REPT(text,number_times) Repeats text a given number of times.
RIGHT =RIGHT(text,num_chars) Returns the last character or characters in a text string based on the number of characters you specify.
SEARCH =SEARCH(find_text, within_text,start_num) Returns the number of the character at which a specific character or text string is first found, reading from left to right. SEARCH is not case sensitive and can include wildcard characters.
SEARCHB =SEARCHB(find_text, within_text,start_num) Returns the number of the character at which a specific character or text string is first found, based on its byte position, reading from left to right.
SUBSTITUTE =SUBSTITUTE(text,old_text, new_text,instance_num) Substitutes new_text for old_text in a text string.
T =T(value) Returns the text referred to by value.
TEXT =TEXT(value,format_text) Converts a value to text in a specific number format.
TRIM =TRIM(text) Removes all spaces from text except for single spaces between words.
UPPER =UPPER(text) Converts text to uppercase.
VALUE =VALUE(text) Converts a text string that represents a number to a number.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint