Excel - Text Functions

URL: https://www.tutorialspoint.com/advanced_excel_functions/advanced_excel_text_functions.htm

Microsoft Excel is enriched with a bundle of Text functions, providing deep insights into manipulating and extracting strings with exciting examples. In this tutorial, we will cover the various types of text functions and their definitions, syntax, benefits, and real-time scenarios. Text Functions are included in the older and latest versions of Microsoft Excel. You can also utilize these functions in Excel for the web.

You can insert these text functions by selecting theText Functionscategory under the Function Library group in the Formula tab or theInsert Functionto add the specific Text function in the worksheet.

ARRAYTOTEXT

Converts an array or range of values into a text string.

ASC

Converts a character into its corresponding ASCII value.

BAHTTEXT

Converts a number to Thai text and adds a suffix of “Baht.”

CHAR

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.

CLEAN

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.

CODE

Returns a numeric code for the first character in a text string. The returned code corresponds to the ANSI character set for the Windows System.

CONCAT

Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.

CONCATENATE

Joins two or more text strings into one string.

DBCS

Converts a single-byte character (SBCS) to a double-byte character (DBCS) which is primarily used for languages like Japanese, Chinese, and Korean.

DOLLAR

Converts a number to text format and applies a currency symbol. The name of the Function and the symbol that it applies depend upon your language settings.

Exact

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.

FIND

FIND and FINDB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

FIXEDFINDB

Find the position of a substring within a string, counting double-byte characters as two characters.

FIXED

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.

LEFT

Returns the first character or characters in a text string, based on the number of characters you specify.

LEFTB

Extracts a specified number of bytes from the beginning of a string.

LEN

LENreturns the number of characters in a text string.LENBreturns the number of bytes used to represent the characters in a text string.

LENB

Returns the number of bytes used to represent a string, useful for double-byte character sets (DBCS) like Japanese, Chinese, and Korean.

LOWER

Converts all uppercase letters in a text string to lowercase.

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.

MIDB

Extracts a specified number of bytes from a string, starting at a specified position.

NUMBERVALUE

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

PHONETIC

Extracts phonetic characters (furigana) from Japanese text.

PROPER

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.

REPLACE

Replaces part of a text string, based on the number of characters you specify, with a different text string.

REPLACEB

Replace a specified number of bytes in a string, starting at a given position.

REPT

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

RIGHT

Returns the last character or characters in a text string, based on the number of characters you specify.

RIGHTB

Extracts a specified number of bytes from the end of a string, useful for handling double-byte character sets (DBCS) in languages like Japanese, Chinese, and Korean.

SEARCH

The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

SEARCHB

Find the position of a substring within a string, counting bytes instead of characters.

SUBSTITUTE

Substitutes new_text for old_text in a text string.

T

The T function returns the text referred to by value.

TEXT

Converts a numeric value to text and lets you specify the display formatting by using special format strings.

TEXTAFTER

Returns the text that appears after a specified substring within a text string

TEXTBEFORE

Returns the text that appears before a specified substring within a text string.

TEXTJOIN

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.

TEXTSPLIT

Splits text strings into an array based on specified delimiters.

TRIM

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.

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 the text.

UPPER

Converts text to uppercase.

VALUE

Converts a text string that represents a number to a number.

VALUETOTEXT

Converts a numeric value into text format.