Text Functions - Proper, Left, Right, Concatenate, Exact, Find, Search, Mid, Replace, Text, Replace

Text Functions

Excel has many functions to offer when it comes to manipulating text strings.

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)

=PROPER(text)

Left

Returns a specified number of characters from the start of a supplied text string

=LEFT(text, num_chars)

Right

Returns a specified number of characters from the end of a supplied text string

=RIGHT(text, num_chars)

Concatenate

Joins together two or more text strings

=CONCATENATE(text1, text2, text3, ....)

Exact

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

=EXACT(text1, text2, text3, ...)

Find

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

=FIND(find_text, within_text, start_num)

Search

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

=SEARCH(find_text, within_text, start_num)

Mid

Returns a specified number of characters from the middle of a supplied text string

=MID(text, start_num, num_chars)

Replace

Replaces all or part of a text string with another string (from a user supplied position)

=REPLACE(old_text, start_num, num_chars, new_text)

Text

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

=TEXT(value, format_text)

Value

Converts a text string into a numeric value

=VALUE(text)

Trim

Removes duplicate spaces, and spaces at the start and end of a text string

=TRIM(text)

218KB

text formulas.xlsx

Text Formula Examples