Complete List of Excel Text Functions

Excel TrainingThis is the complete list of every Excel Function for text. For each function I have provided a description, the formula and in some cases an example. Where text is necessary I have included the word ‘text’ as an example and where a target cell is specified I have used ‘A1′. Where appropriate I have provided links to other uses for the functions.

This is very much a quick reference guide, if you need help using these formulas you should come on our Excel Training course aimed at beginners. If you’re already up to scratch with all of these functions and are looking for something more advanced then our Advanced Excel Training Course is right for you.

Microsoft Excel 2007 Text Functions

  • ASC

Description: Changes double-byte characters (full-width) into single-byte characters (half-width) for Double-byte characters sets (DBCS).

Formula: =ASC(“text”)

  • BAHHTEXT

Description: Changes numbers into Thai currency by changing the number to Thai text and adding the suffix “Baht”

Formula: =BAHTTEXT(A1)

  • CHAR

Description: Displays the character defined by a number in a character set (Macintosh or ANSI on Windows).

Formula: =CHAR(86)

Example: Excel Text Functions CHAR

  • CLEAN

Description: Removes non-printable characters from text.

Formula: =CLEAN(A1)

  • CODE

Description: The opposite of CHAR, this returns the numeric code for a character.

Formula: =CODE(V)

Example: Excel Text Functions CODE

  • CONCATENATE

Description: Combines multiple text entries into one.

Formula: =CONCATENATE(A1,B1)

Example: Excel Text functions CONCATENATE

If you are looking to save time on Excel then try these Top 10 Time Saving Excel Formulas

  • DOLLAR

Description: Converts a number to text and adds a currency symbol depending on language settings. More on Excel Dollar Signs.

Formula: =DOLLAR(A1)

Example: Excel Text Functions DOLLAR

  • EXACT

Description: Looks at two text strings and shows TRUE if they are identical, FALSE if not. Is case-sensitive but ignores formatting.

Formula: =EXACT(A1,A2)

  • FIND

Description: Finds a text value within a string of text. The example formula would find the first ‘S’ within the cell specified. Find is case-sensitive – use SEARCH if you don’t want a case-sensitive search.

Formula: =FIND(“S”,A1)

If you are looking to find numbers then use our Finding Highest and Lowest Numbers in Excel guide.

  • FIXED

Description: Converts a number to text and specifies the number of decimals.

Formula: =FIXED(A1,1)

Example: Excel Text Functions FIXED

  • JIS

Description: The reverse of ASC, converts single-byte characters into double-byte characters.

Formula: =JIS(“text”)

  • LEFT

Description: Returns a specified number of characters from the left of a string of text.

Formula: =LEFT(A1,3)

Example: Excel Text Functions LEFT

  • LEN

Description: Returns the number of characters in a string of text.

Formula: =LEN(A1)

Example: Excel Text Functions LEN

  • LOWER

Description: Converts all text in a cell to lowercase.

Formula: =LOWER(A1)

Example: Excel Text Functions LOWER

  • MID

Description: Returns a specified number of characters from a string of text, from a specified start point. Good if you have a range of data which all has the same first word and you would like the data without it.

Formula: =MID(A1,9,5)

Example: Excel Text Functions MID

  • PROPER

Description: Capitalises the first letter of each word in a string of text and converts all other letters into lowercase.

Formula: =PROPER(A1)

Example: Excel Text Functions PROPER

  • REPLACE

Description: Replaces a specified part of text with a new set of characters at defined point in the string of text.

Formula: =REPLACE

Example: Excel Text Functions REPLACE

  • REPT

Description: Repeats text a specified number of times.

Formula: =REPT(“text”,3)

Example: Excel Text Functions REPT

  • RIGHT

Description: Similar to the LEFT function but returns text from the right of a string of text.

Formula: =RIGHT(A1,3)

Example: =Excel Text Functions RIGHT

  • SEARCH

Description: Similar to FIND but is not case-sensitive. Finds the position of a specified text value within another.

Formula: =SEARCH(“S”,A1)

  • SUBSTITUTE

Description: Replace a text value with a new text value within a cell. Use this for specific text, use REPLACE if you want to specify by location.

Formula: =SUBSTITUTE(A1,”Training”,”Course”)

Example: Excel Text Functions SUBSTITUTE

  • TEXT

Description: Converts a value to text using a numeric format.

Formula: =A1&” is “&TEXT(A3,”0%”)&” great!”

Example: Excel Text Functions TEXT

  • TRIM

Description: Removes all spaces except those within words.

Formula: =TRIM(A1)

Example: Excel Text Functions TRIM

  • UPPER

Description: Changes all text into uppercase

Formula: =UPPER(A1)

Example: Excel Text Functions UPPER

Hopefully you will be able to find these formulas useful. If you have any problems leave a comment and we will try and help.

Stay tuned for more Excel Function lists.

Tags

, , , , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment