Monday, 27 January 2025

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)
Result11


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)
Result7


12. SEARCH

Description: Finds the position of a substring within a text string (not case-sensitive).

Data"Excel Functions"
Formula=SEARCH("fun", A1)
Result7


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.

Data65
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)
Result65


18. EXACT

Description: Compares two text strings (case-sensitive).

Data"Hello" (A1), "hello" (B1)
Formula=EXACT(A1, B1)
ResultFALSE


19. VALUE

Description: Converts a text string to a number.

Data"123"
Formula=VALUE(A1)
Result123


20. TEXT

Description: Formats a number as text with a specified format.

Data1234.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.

Data1234.567
Formula=FIXED(A1, 2)
Result"1,234.57"


24. DOLLAR

Description: Converts a number to text using currency format.

Data1234.567
Formula=DOLLAR(A1, 2)
Result"$1,234.57"


25. UNICHAR

Description: Returns the Unicode character for a number.

Data9731
Formula=UNICHAR(A1)
Result"☃"


26. UNICODE

Description: Returns the Unicode number for the first character in a text string.

Data"☃"
Formula=UNICODE(A1)
Result9731


27. BAHTTEXT

Description: Converts a number to Thai text.

Data1234.567
Formula=BAHTTEXT(A1)
Result"หนึ่งพันสองร้อยสามสิบสี่บาทห้าสิบเจ็ดสตางค์"


28. NUMBERVALUE

Description: Converts text to a number, ignoring non-numeric characters.

Data"1,234.56"
Formula=NUMBERVALUE(A1)
Result1234.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)
Result5


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)
Result3


38. FINDB

Description: Finds the position of a substring within a text string, based on bytes (case-sensitive).

Data"Hello"
Formula=FINDB("ll", A1)
Result3


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)
ResultTRUE


45. ISNONTEXT

Description: Checks if a value is not text.

Data123
Formula=ISNONTEXT(A1)
ResultTRUE


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")
Result2


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