Text Functions in Excel
Text Functions in Excel
Excel boasts a vast library of functions, but don’t be overwhelmed! We’ll explore the most essential functions grouped into intuitive categories. Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.
Text Functions
Transform and Cleanse Your Text Data: Text data in your spreadsheets needs some TLC? Text Functions are your one-stop shop! Whether you need to combine text strings (CONCATENATE), extract specific characters (LEFT, RIGHT), or format casing (UPPER, LOWER), these functions offer unparalleled control over text manipulation and cleaning.
- Combine text strings (CONCATENATE).
- Extract specific characters (LEFT, RIGHT).
- Format casing (UPPER, LOWER).
- Clean and format customer data or extract information from text strings.
| Function | Description | Syntax and Formula |
| ASC | The ASC function in Microsoft Excel is used to convert a character into its corresponding ASCII code. | Syntax: ASC(text) Formula: ASC(“A”)The result of this formula would be 65, which is the ASCII code for the letter A. |
| ARRAYTOTEXT | The ARRAYTOTEXT function in Excel converts an array of values into a text string. | Syntax: ARRAYTOTEXT(array, delimiter, [text_qualifier]) Formula: ARRAYTOTEXT({1,2,3,4,5},”,”,”‘”)Result: ‘1’,’2′,’3′,’4′,’5′ |
| BAHTTEXT | BAHTTEXT is an Excel function that converts a number to Thai text. | Syntax: BAHTTEXT(number) Formula: BAHTTEXT(12345)Result: ??????????????????????????????????????? |
| CHAR | The CHAR function returns the character specified by the number code. | Syntax: CHAR(number) Formula: CHAR(65)This will return the character “A”. |
| CLEAN | The CLEAN function removes all non-printable characters from a text string. | Syntax: CLEAN(text) Formula: CLEAN(“Hello!@#$%^&*World”) Result: HelloWorld |
| CODE | The CODE function is used to convert a text string into a code with a corresponding numerical value. | Syntax: CODE(text) Formula: CODE(“A”) returns 65. |
| CONCAT | The CONCAT function in Excel is used to join two or more text strings together. Supports Ranges. | Syntax: CONCAT(text1, [text2], …) Formula: CONCAT(“Hello “, “World”) , Result: Hello World CONCAT(A1:A5), concatenated all information in the RangeA1:A5 |
| CONCATENATE | The CONCATENATE function is used to join two or more text strings together. Can not handle ranges. | Syntax: CONCATENATE(text1, [text2], [text3], …) Formula: CONCATENATE(“Hello”,” “,”World”) Result: Hello World |
| DBCS | The DBCS function is a database function in Microsoft Excel that allows you to extract data from a database. | Syntax: DBCS(database, field, criteria) Formula: DBCS(“customers”,”name”,”state=’CA’”) |
| DOLLAR | The DOLLAR function converts a number to text, using a currency format. | Syntax: DOLLAR(number, [decimals]) Formula: DOLLAR(A2, 2)This example will convert the number in cell A2 to text, using two decimal places. |
| EXACT | The EXACT function compares two text strings and returns TRUE if the two strings are exactly the same, including case and any spaces. | Syntax: EXACT(text1, text2) Formula: EXACT(“Apple”,”apple”)Result: FALSE |
| FIXED | The FIXED function in Excel is used to round a number to a specified number of decimal places and display it as a text string with a fixed number of decimal places. | Syntax: FIXED(number, [decimals], [no_commas]) Formula: FIXED(123.4567,2,TRUE)Result: 123.46 |
| LEFT, LEFTB | LEFT:The LEFT function in Excel returns the leftmost characters from a text string. And LEFTB returns the leftmost characters based on the bytes. | Syntax: LEFT(text, [num_chars])text: The text string from which you want to extract the characters.num_chars: [optional] The number of characters you want to extract from the left of the text string. Formula: :LEFT(“Excel”, 2)Result: ExLEFTB:The LEFTB function in Excel returns the leftmost characters from a text string based on the number of bytes. Syntax: LEFTB(text, [num_bytes])text: The text string from which you want to extract the characters.num_bytes: [optional] The number of bytes you want to extract from the left of the text string. Example:=LEFTB(“Excel”, 3)Result: Exc” |
| LEN, LENB | LEN:The LEN function is used to count the number of characters in a text string. And LENB returns the number of bytes used to represent the characters in a text string. | Syntax: LEN(text) Formula: LEN(“Hello World”)Result: 11LENBs:Description: The LENBs function is used to count the number of bytes in a text string.Syntax: LENBs(text)LENBs(“Hello World”)Result: 11 |
| RIGHT, RIGHTB | The Right function in Excel returns the rightmost characters from a text string. And RIGHTB returns the rightmost characters based on the bytes. | Syntax: RIGHT(text, [num_chars]) Formula: RIGHT(“Excel Sheet”, 5)Result:Sheet |
| LOWER | The LOWER function in Excel converts all letters in a text string to lowercase. | Syntax: LOWER(text) Formula: LOWER(“EXCEL FUNCTION”)Result: excel function |
| NUMBERVALUE | The NUMBERVALUE function converts a text string that represents a number to a number. | Syntax: NUMBERVALUE(text, [decimal_separator], [group_separator]) Formula: NUMBERVALUE(“$1,234.56”)This would return 1234.56. |
| PHONETIC | The PHONETIC function is used to convert text into phonetic pronunciation. This function is useful for creating a phonetic representation of names or words, which can help differentiate between similar sounding words. | Syntax: PHONETIC(text) Formula: PHONETIC(“Excel”)Result: “ECKS-ELL” |
| PROPER | The PROPER function is used to convert a text string to proper case, which means that the first letter of each word is capitalized and all other letters are lowercase. | Syntax: PROPER(text) Formula: PROPER(“this is a test”)Result: This Is A Test |
| REPT | The REPT function in Excel is used to repeat a text string a specified number of times. | Syntax: REPT(text, number_times) Formula: REPT(“Hello”, 3)Result: HelloHelloHello |
| SUBSTITUTE | The SUBSTITUTE function in Excel replaces existing text with new text in a provided text string. | Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num]) Formula: SUBSTITUTE(A2,”red”,”blue”)This example would replace all instances of “red” with “blue” in the text string in cell A2. |
| T.TEST | T.TEST is a statistical function in Excel that calculates the probability associated with a Student’s t-test. It is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. | Syntax: T.TEST(array1, array2, tails, type)Array1: The first array or range of dataArray2: The second array or range of dataTails: The number of tails in the test, either 1 or 2Type: The type of t-test to be used, either “1” for a paired two-sample t-test or “2” for a two-sample t-test with unequal variances Formula: T.TEST(A1:A10, B1:B10, 2, 2)This example would calculate the probability associated with a two-sample t-test with unequal variances using the data in range A1:A10 and B1:B10. |
| TEXT | The TEXT function is used to convert a value to text in a specific number format. | Syntax: TEXT(value, format_text) Formula: TEXT(A1,”$#,##0.00″) |
| TEXTAFTER | The TEXTAFTER function returns the characters after a given character or characters in a text string. | Syntax: TEXTAFTER(text, character) Formula: TEXTAFTER(“Hello World”,”o”)Result: ” World” |
| TEXTBEFORE | The TEXTBEFORE function is a text function in Microsoft Excel that returns the text before a specific character in a string. | Syntax: TEXTBEFORE(text, delimiter) Formula: TEXTBEFORE(“www.example.com”,”.”)Result: www |
| TEXTJOIN | The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that is combined. | Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) Formula: TEXTJOIN(“, “, TRUE, B2:B4)This example combines the text from cells B2, B3, and B4, and includes a comma and space between each value. The “TRUE” argument ignores any empty cells in the range. |
| TEXTSPLIT | The TEXTSPLIT function splits a text string into multiple parts based on a specified delimiter. | Syntax: TEXTSPLIT(text, delimiter) Formula: TEXTSPLIT(“Apple,Banana,Orange”,”,”)This formula will return the result “Apple, Banana, Orange”. |
| TRIM | The TRIM function removes all spaces from a text string except for single spaces between words. | Syntax: TRIM(text) Formula: TRIM(” This is a test “)Result: “This is a test” |
| UNICHAR | UNICHAR is an Excel function that returns the Unicode character that corresponds to the given numeric code. | Syntax: UNICHAR(number) Formula: UNICHAR(65)This will return the character “A” in the cell. |
| UNICODE | The UNICODE function is a built-in function in Microsoft Excel that returns the numerical value of a character or the first character in a string. | Syntax: UNICODE(text) Formula: UNICODE(“A”)Result: 65 |
| UPPER | The UPPER function in Excel converts all text in a cell to uppercase. | Syntax: UPPER(text) Formula: UPPER(“Hello World”)Result: HELLO WORLD |
| VALUE | The VALUE function converts a text string that represents a number to a number. | Syntax: VALUE(text) Formula: VALUE(“2”) returns 2 |
| VALUETOTEXT | VALUETOTEXT is an Excel function that converts a numerical value into a text string. | Syntax: VALUETOTEXT(value, [format_text]) Formula: VALUETOTEXT(A1,”$#,##0.00″)This example will convert the numerical value in cell A1 into a text string in the format “$#,##0.00”. |
You May Like to Browers More
