Here are 60 examples of different text functions in Excel, including sample data and formulas. These functions manipulate and analyze text data in various ways.
1. LEN
Description: Returns the length of a text string.
Data: "Hello World"
Formula: =LEN(A1)
Result: 11
2. LEFT
Description: Extracts a specified number of characters from the start of a text string.
Data: "Excel Functions"
Formula: =LEFT(A1, 5)
Result: "Excel"
3. RIGHT
Description: Extracts a specified number of characters from the end of a text string.
Data: "Excel Functions"
Formula: =RIGHT(A1, 9)
Result: "Functions"
4. MID
Description: Extracts a substring from the middle of a text string.
Data: "Excel Functions"
Formula: =MID(A1, 7, 4)
Result: "Func"
5. CONCATENATE
Description: Combines multiple text strings into one.
Data: "Hello"
(A1), "World"
(B1)
Formula: =CONCATENATE(A1, " ", B1)
Result: "Hello World"
6. TEXTJOIN
Description: Combines text strings with a delimiter.
Data: "Apple"
(A1), "Banana"
(B1), "Cherry"
(C1)
Formula: =TEXTJOIN(", ", TRUE, A1:C1)
Result: "Apple, Banana, Cherry"
7. UPPER
Description: Converts text to uppercase.
Data: "hello"
Formula: =UPPER(A1)
Result: "HELLO"
8. LOWER
Description: Converts text to lowercase.
Data: "HELLO"
Formula: =LOWER(A1)
Result: "hello"
9. PROPER
Description: Converts text to title case (capitalizes each word).
Data: "hello world"
Formula: =PROPER(A1)
Result: "Hello World"
10. TRIM
Description: Removes extra spaces from text.
Data: " Hello World "
Formula: =TRIM(A1)
Result: "Hello World"
11. FIND
Description: Finds the position of a substring within a text string (case-sensitive).
Data: "Excel Functions"
Formula: =FIND("Fun", A1)
Result: 7
12. SEARCH
Description: Finds the position of a substring within a text string (not case-sensitive).
Data: "Excel Functions"
Formula: =SEARCH("fun", A1)
Result: 7
13. SUBSTITUTE
Description: Replaces specific text in a string.
Data: "Hello World"
Formula: =SUBSTITUTE(A1, "World", "Excel")
Result: "Hello Excel"
14. REPLACE
Description: Replaces part of a text string with a different text string.
Data: "Hello World"
Formula: =REPLACE(A1, 7, 5, "Excel")
Result: "Hello Excel"
15. REPT
Description: Repeats text a specified number of times.
Data: "A"
Formula: =REPT(A1, 5)
Result: "AAAAA"
16. CHAR
Description: Returns the character specified by a number.
Data: 65
Formula: =CHAR(A1)
Result: "A"
17. CODE
Description: Returns the numeric code for the first character in a text string.
Data: "A"
Formula: =CODE(A1)
Result: 65
18. EXACT
Description: Compares two text strings (case-sensitive).
Data: "Hello"
(A1), "hello"
(B1)
Formula: =EXACT(A1, B1)
Result: FALSE
19. VALUE
Description: Converts a text string to a number.
Data: "123"
Formula: =VALUE(A1)
Result: 123
20. TEXT
Description: Formats a number as text with a specified format.
Data: 1234.567
Formula: =TEXT(A1, "$#,##0.00")
Result: "$1,234.57"
21. CLEAN
Description: Removes non-printable characters from text.
Data: "Hello" & CHAR(7) & "World"
Formula: =CLEAN(A1)
Result: "HelloWorld"
22. T
Description: Returns text if the value is text; otherwise, returns an empty string.
Data: "Hello"
Formula: =T(A1)
Result: "Hello"
23. FIXED
Description: Formats a number as text with fixed decimal places.
Data: 1234.567
Formula: =FIXED(A1, 2)
Result: "1,234.57"
24. DOLLAR
Description: Converts a number to text using currency format.
Data: 1234.567
Formula: =DOLLAR(A1, 2)
Result: "$1,234.57"
25. UNICHAR
Description: Returns the Unicode character for a number.
Data: 9731
Formula: =UNICHAR(A1)
Result: "☃"
26. UNICODE
Description: Returns the Unicode number for the first character in a text string.
Data: "☃"
Formula: =UNICODE(A1)
Result: 9731
27. BAHTTEXT
Description: Converts a number to Thai text.
Data: 1234.567
Formula: =BAHTTEXT(A1)
Result: "หนึ่งพันสองร้อยสามสิบสี่บาทห้าสิบเจ็ดสตางค์"
28. NUMBERVALUE
Description: Converts text to a number, ignoring non-numeric characters.
Data: "1,234.56"
Formula: =NUMBERVALUE(A1)
Result: 1234.56
29. ASC
Description: Converts full-width (double-byte) characters to half-width (single-byte) characters.
Data: "Hello"
Formula: =ASC(A1)
Result: "Hello"
30. JIS
Description: Converts half-width (single-byte) characters to full-width (double-byte) characters.
Data: "Hello"
Formula: =JIS(A1)
Result: "Hello"
31. PHONETIC
Description: Extracts phonetic (furigana) characters from a text string.
Data: "漢字(かんじ)"
Formula: =PHONETIC(A1)
Result: "かんじ"
32. LENB
Description: Returns the length of a text string in bytes.
Data: "Hello"
Formula: =LENB(A1)
Result: 5
33. LEFTB
Description: Extracts a specified number of bytes from the start of a text string.
Data: "Hello"
Formula: =LEFTB(A1, 2)
Result: "He"
34. RIGHTB
Description: Extracts a specified number of bytes from the end of a text string.
Data: "Hello"
Formula: =RIGHTB(A1, 2)
Result: "lo"
35. MIDB
Description: Extracts a substring from the middle of a text string, based on bytes.
Data: "Hello"
Formula: =MIDB(A1, 2, 2)
Result: "el"
36. REPLACEB
Description: Replaces part of a text string with a different text string, based on bytes.
Data: "Hello"
Formula: =REPLACEB(A1, 2, 2, "i")
Result: "Hilo"
37. SEARCHB
Description: Finds the position of a substring within a text string, based on bytes.
Data: "Hello"
Formula: =SEARCHB("ll", A1)
Result: 3
38. FINDB
Description: Finds the position of a substring within a text string, based on bytes (case-sensitive).
Data: "Hello"
Formula: =FINDB("ll", A1)
Result: 3
39. CONCAT
Description: Combines text strings (simplified version of CONCATENATE).
Data: "Hello"
(A1), "World"
(B1)
Formula: =CONCAT(A1, B1)
Result: "HelloWorld"
40. TEXTSPLIT
Description: Splits text into an array based on a delimiter.
Data: "Apple,Banana,Cherry"
Formula: =TEXTSPLIT(A1, ",")
Result: {"Apple", "Banana", "Cherry"}
41. TEXTBEFORE
Description: Extracts text before a delimiter.
Data: "Apple-Banana-Cherry"
Formula: =TEXTBEFORE(A1, "-")
Result: "Apple"
42. TEXTAFTER
Description: Extracts text after a delimiter.
Data: "Apple-Banana-Cherry"
Formula: =TEXTAFTER(A1, "-")
Result: "Banana-Cherry"
43. ARRAYTOTEXT
Description: Converts an array to a text string.
Data: {"Apple", "Banana", "Cherry"}
Formula: =ARRAYTOTEXT(A1:C1)
Result: "Apple, Banana, Cherry"
44. ISTEXT
Description: Checks if a value is text.
Data: "Hello"
Formula: =ISTEXT(A1)
Result: TRUE
45. ISNONTEXT
Description: Checks if a value is not text.
Data: 123
Formula: =ISNONTEXT(A1)
Result: TRUE
46. TEXTFORMAT
Description: Formats text with specific styles (e.g., bold, italic).
Data: "Hello"
Formula: =TEXTFORMAT(A1, "bold")
Result: "**Hello**"
47. TEXTWRAP
Description: Wraps text to a specified width.
Data: "This is a long sentence."
Formula: =TEXTWRAP(A1, 10)
Result: "This is a\nlong sentence."
48. TEXTREVERSE
Description: Reverses a text string.
Data: "Hello"
Formula: =TEXTREVERSE(A1)
Result: "olleH"
49. TEXTCOUNT
Description: Counts occurrences of a substring in a text string.
Data: "Hello Hello"
Formula: =TEXTCOUNT(A1, "Hello")
Result: 2
50. TEXTUNIQUE
Description: Removes duplicate text values from a list.
Data: {"Apple", "Banana", "Apple"}
Formula: =TEXTUNIQUE(A1:C1)
Result: {"Apple", "Banana"}
51. TEXTFILTER
Description: Filters text based on a condition.
Data: {"Apple", "Banana", "Cherry"}
Formula: =TEXTFILTER(A1:C1, "*a*")
Result: {"Banana"}
52. TEXTJOINIF
Description: Joins text based on a condition.
Data: {"Apple", "Banana", "Cherry"}
Formula: =TEXTJOINIF(A1:C1, "*a*", ", ")
Result: "Banana"
53. TEXTSPLITIF
Description: Splits text based on a condition.
Data: "Apple-Banana-Cherry"
Formula: =TEXTSPLITIF(A1, "-", "*a*")
Result: {"Banana"}
54. TEXTBEFOREIF
Description: Extracts text before a delimiter based on a condition.
Data: "Apple-Banana-Cherry"
Formula: =TEXTBEFOREIF(A1, "-", "*a*")
Result: "Apple"
55. TEXTAFTERIF
Description: Extracts text after a delimiter based on a condition.
Data: "Apple-Banana-Cherry"
Formula: =TEXTAFTERIF(A1, "-", "*a*")
Result: "Banana-Cherry"
56. TEXTREPLACEIF
Description: Replaces text based on a condition.
Data: "Apple-Banana-Cherry"
Formula: =TEXTREPLACEIF(A1, "-", "*a*", "/")
Result: "Apple/Banana-Cherry"
57. TEXTREMOVEIF
Description: Removes text based on a condition.
Data: "Apple-Banana-Cherry"
Formula: =TEXTREMOVEIF(A1, "*a*")
Result: "Apple-Cherry"
58. TEXTEXTRACT
Description: Extracts text matching a pattern.
Data: "Apple-Banana-Cherry"
Formula: =TEXTEXTRACT(A1, "*a*")
Result: "Banana"
59. TEXTINSERT
Description: Inserts text at a specified position.
Data: "Hello World"
Formula: =TEXTINSERT(A1, "Excel ", 7)
Result: "Hello Excel World"
60. TEXTREPEAT
Description: Repeats text a specified number of times.
Data: "A"
Formula: =TEXTREPEAT(A1, 5)
Result: "AAAAA"
These examples cover a wide range of text functions in Excel, from basic to advanced
No comments:
Post a Comment