Excel Text functions

The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.

For better understanding and easy to learn I separated or grouped functions with a brief description which is as below. We will learn each and every function details in next articles.

 

Functions to Convert Excel Data Types
  BAHTTEXT Converts a number, plus the suffix “Baht” into Thai text
  DOLLAR Converts a supplied number into text, using a currency format
  FIXED Rounds a supplied number to a specified number of decimal places, and then converts this into text
  TEXT Converts a supplied value into text, using a user-specified format
  VALUE Converts a text string into a numeric value
  NUMBERVALUE Converts text to a number, in a locale-independent way (New in Excel 2013)

 

 

Cutting Up & Piecing Together Text Strings
CONCATENATE Joins together two or more text strings
LEFT Returns a specified number of characters from the start of a supplied text string
MID Returns a specified number of characters from the middle of a supplied text string
RIGHT Returns a specified number of characters from the end of a supplied text string
REPT Returns a string consisting of a supplied text string, repeated a specified number of times

 

 

Functions to Convert Between Upper & Lower Case
LOWER Converts all characters in a supplied text string to lower case
PROPER Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case)
UPPER Converts all characters in a supplied text string to upper case

 

 

Replacing / Substituting Parts of a Text String
REPLACE Replaces all or part of a text string with another string (from a user supplied position)
SUBSTITUTE Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text

 

 

Information Functions
LEN Returns the length of a supplied text string
FIND Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)
SEARCH Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)
EXACT Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive)
T Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

 

 

Converting Between Characters & Numeric Codes
CHAR Returns the character that corresponds to a supplied numeric value
CODE Returns the numeric code for the first character of a supplied string
UNICHAR Returns the Unicode character that is referenced by the given numeric value (New in Excel 2013)
UNICODE Returns the number (code point) corresponding to the first character of a supplied text string (New in Excel 2013)

 

 

Functions to Remove Extra Characters
CLEAN Removes all non-printable characters from a supplied text string
TRIM Removes duplicate spaces, and spaces at the start and end of a text string

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment