Text Function in Excel

Hello All. In this section, let’s look into all the Text functions available. Here is the table with the usage and syntax for all the Text functions in Excel

 Function Usage Syntax CHAR Return a specific character based on the numerical value input =CHAR(number) CLEAN Removes all non-printable characters from the text =CLEAN(text) CODE Returns the numeric code for the first character in the text string =CODE(text) CONCAT Joins a list or range of text strings =CONCAT(text1, text2,…) CONCATENATE Joins several text strings into one =CONCATENATE(text1, text2,…) DOLLAR Convert a number to text in currency format =DOLLAR(number,[decimals]) EXACT Compare 2 x text strings =EXACT(text1, text2) FIND Returns the location of text in a text string. Case sensitive =FIND(text, within text, [starting number]) FIXED Format the number with fixed decimals =FIXED(number, [decimals], [no_commas]) LEFT Returns the specified number of characters from start of text string =LEFT(text,[num_chars]) LEN Get the length of the text string =LEN(text) LOWER Convert the text string to lower case =LOWER(text) MID Extract characters from middle of the text string, given the starting position and length =MID(text, start_num, num_chars) NUMBERVALUE Converts a number in text format to numeric value =NUMBERVALUE(text,[decimal separator],[group separator]) PROPER Capitalize the first letter in each word =PROPER(text) REPLACE Replace text string with another text string =REPLACE(old text, start_num, num_chars, new text) REPT Repeat text a given number of times =REPT(text, number_times) RIGHT Returns the specified number of characters towards end of a text string =RIGHT(text, [num_chars]) SEARCH Returns the location of text in a string (From left to right) =SEARCH(find text,within text, [start num]) SUBSTITUTE Replace existing text with new text in the text string =SUBSTITUTE(text, old text, new text, [instance num]) TEXT Converts a value to text in a specified number format =TEXT(value, format_text) TEXTJOIN Concatenates a list or range of text strings with a delimiter =TEXTJOIN(delimiter, ignore empty, text1, text2,…) TRIM Remove spaces from text except for single space between words =TRIM(text) UNICHAR Get Unicode character by number =UNICHAR(number) UNICODE Get number corresponding to the first character of the text =UNICODE(text) UPPER Convert text into upper case =UPPER(text) VALUE Convert a number in text format into a number format =VALUE(text)
I hope you liked this one pager about all the Text functions in Excel. Shortly, I will insert hyperlink for each of the function explaining them in detail.