EXCEL : Text Formula & Syntax
- Com Bird
- Oct 28, 2023
- 4 min read
Updated: Jan 4, 2024
1) ARRAYTOTEXT function
Description
The ARRAYTOTEXT function returns an array of text values from any specified range. It passes text values unchanged, and converts non-text values to text.
Syntax
ARRAYTOTEXT(array, [format])
2) BAHTTEXT function
Description
Converts a number to Thai text and adds a suffix of "Baht."
Syntax
BAHTTEXT(number)
3) CHAR Function
Description
Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.
Syntax
CHAR(number)
4) CLEAN function
Description
Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
Syntax
CLEAN(text)
5) CODE function
Description
Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
Syntax
CODE(text)
6) CONCAT function
Description
The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.
CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel
Syntax
CONCAT(text1, [text2],…)
7) DOLLAR function
Description
The DOLLAR function, one of the TEXT functions, converts a number to text using currency format, with the decimals rounded to the number of places you specify. DOLLAR uses the $#,##0.00_);($#,##0.00) number format, although the currency symbol that is applied depends on your local language settings..
Syntax
DOLLAR(number, [decimals])
8) EXACT function
Description
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
Syntax
EXACT(text1, text2)
9) FIND function
Description
Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters.
Syntax
FIND(find_text,within_text,start_num)
10) FIXED function
Description
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Syntax
FIXED(number, [decimals], [no_commas])
11) LEFT function
Description
LEFT returns the first character or characters in a text string, based on the number of characters you specify..
Syntax
LEFT(text,num_chars)
12) LOWER function
Description
Converts all uppercase letters in a text string to lowercase
Syntax
LOWER(text)
13) MID function
Description
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
Syntax
MID(text,start_num,num_chars)
14) NUMBERVALUE function
Description
Converts text to a number, in a locale-independent way.
Syntax
NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])
15) PROPER function
Description
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
Syntax
PROPER(text)
16) REPLACE function
Description
REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
Syntax
REPLACE(old_text,start_num,num_chars,new_text)
17) REPT function
Description
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
Syntax
AND(Logical1,[Logical2],…)
18) RIGHT function
Description
RIGHT returns the last characters in a text string, based on the number of characters you specify.
Syntax
RIGHT(text,num_chars)
19) SEARCH function
Description
SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE functions to change the text.
Syntax
SEARCH(find_text,within_text,start_num)
20) SUBSTITUTE function
Description
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num]).
21) T function
Description
Returns the text referred to by value.
Syntax
T(value)
22) TEXT function
Description
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Syntax
TEXT(value, format_text)
23) TEXTJOIN function
Description
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
24) TRIM function
Description
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
Syntax
TRIM(text)
25) UNICHAR function
Description
Returns the Unicode character that is referenced by the given numeric value.
Syntax
UNICHAR(number)
26) UNICODE function
Description
Returns the number (code point) corresponding to the first character of the text.
Syntax
UNICODE(text)
27) UPPER function
Description
Converts text to uppercase.
Syntax
UPPER(text)
28) VALUE function
Description
Converts a text string that represents a number to a number.
Syntax
VALUE(text)
29) VALUETOTEXT function
Description
The VALUETOTEXT function returns text from any specified value. It passes text values unchanged, and converts non-text values to text.
Syntax
VALUETOTEXT(value, [format])
Comments