Excel Training London: 60 uses of text functions

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: "Ｈｅｌｌｏ"
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: "Ｈｅｌｌｏ"

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