Text Functions

This page lists the current, built-in Excel Text Functions. These functions are provided by Excel, to help you to work with text strings. They include functions to return information about a text string, to apply formatting to a text string, to convert between text and other data types, and to cut up and join together text strings.

The functions have been grouped into categories, to help you to find the function you need. Each function page provides a full description of the function, with examples of use and common errors.

Basic Excel

This page covers some of the Basic Excel skills that will help you when you are using functions and formulas in Excel.
Click on the links below or select one of the options from the "Quick Selection" menu on the right.
Most of the useful Excel basics are covered by the links below, but if you are a complete beginner, you might find more useful tips on the Microsoft Office Basic Excel page.

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

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

Information Functions

-

• EXACT

  Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive)

• FIND

  Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)

• LEN

  Returns the length of a supplied text string

• SEARCH

  Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)

• T

  Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

Financial Functions

-

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

• NUMBERVALUE

  Converts text to a number, in a locale-independent way

• TEXT

  Converts a supplied value into text, using a user-specified format

• VALUE

  Converts a text string into a numeric value

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

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

• REPT

  Returns a string consisting of a supplied text string, repeated a specified number of times

• RIGHT

  Returns a specified number of characters from the end of a supplied 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

• UNICODE

  Returns the number (code point) corresponding to the first character of a supplied text string