Buzz
Excel supports various function groups to facilitate quick data processing, and one of them is the group of text and string processing functions.
This article compiles the text and string processing functions available in Excel.
1. ASC Function.
Syntax: ASC(text).
Function: The ASC function aids in converting double-byte characters to single-byte characters.
2. BATHTEXT Function.
Syntax: BATHTEXT(number).
Translate digits into words (Thai-language) and append the suffix 'Bath' at the end.
3. CHAR Function.
Syntax: CHAR(number).
Converts a numeric code in the ANSI character set (with a value range of 1-255) to the corresponding character.
4. CLEAN Function.
Syntax: CLEAN(text).
Functionality: Removes all non-printable characters from the string.
5. CODE Function.
Syntax: CODE(text).
Functionality: Returns the numeric code of the first character in the text string.
6. CONCATENATE Function.
Syntax: CONCATENATE(text1, text2…).
Function: Concatenate multiple strings into one.
7. DOLLAR Function.
Syntax: DOLLAR(number, decimals).
Functionality: Converts a number to currency format (US dollars), with optional thousand separators and customizable rounding.
8. EXACT Function.
Syntax: EXACT(text1, text2).
Functionality: Compares two strings. Returns TRUE if they are identical, FALSE if different. Case-sensitive.
9. FIND Function.
Syntax: FIND(find_text, within_text, start_num).
Functionality: Locates the starting position of a substring (find_text) within a string (within_text), based on the first character.
10. FIXED Function.
Syntax: FIXED(number, decimals, no_commas).
Functionality: Converts a number to text format, with or without thousand separators, and customizable rounding.
11. LEFT Function.
Syntax: LEFT(text, num_chars).
Functionality: Returns one or more characters from the left side of a string, based on the specified number of characters.
12. LEN Function.
Syntax: LEN(text).
Functionality: Counts the number of characters in a string.
13. LOWER Function.
Syntax: LOWER(text).
Functionality: Converts all characters in a text string to lowercase.
14. MID Function.
Syntax: MID(text, start_num, num_chars).
Functionality: Returns one or more consecutive characters within a string, starting from a specified position.
15. PROPER Function.
Syntax: PROPER(text).
Functionality: Capitalizes the first character in a string and converts the remaining characters to lowercase.
16. REPLACE Function.
Syntax: REPLACE(old_text, start_num, num_chars, new_text).
Functionality: Substitutes a portion of a string with another string, with a specified number of characters.
17. REPT Function.
Syntax: REPT(text, times).
Functionality: Repeats a string a specified number of times.
18. RIGHT Function.
Syntax: RIGHT(text, num_chars).
Functionality: Returns one or more characters counting from the right side of a string, based on the specified number of characters.
19. SEARCH Function.
Syntax: SEARCH(find_text, within_text, start_num).
Functionality: Finds the starting position of a substring (find_text) within a string (within_text), based on the first character.
20. SUBSTITUTE Function.
Syntax: SUBSTITUTE(text, old_text, new_text, instance_num).
Functionality: Replaces one string with another within a text string.
21. T Function.
Syntax: T(value).
Functionality: Returns a string if the referenced value is a string; otherwise, returns an empty string.
22. TEXT Function.
Syntax: TEXT(value, format_text).
Functionality: Converts a number to text format using the specified format.
23. TRIM Function.
Syntax: TRIM(text).
Functionality: Removes all unnecessary whitespaces in a text string, retaining spaces used as separators between words.
24. UPPER Function.
Syntax: UPPER(text).
Functionality: Converts all characters in a string to uppercase.
25. VALUE Function.
Syntax: VALUE(text).
Functionality: Converts a string to a number.
I hope this compilation of text and string processing functions proves helpful to you. Best of luck!
