Chapter 32: Text Functions in Excel

Introduction to Text Functions

Text functions in Excel are essential for manipulating and analyzing text data. They allow you to clean, format, and transform text, making data management more efficient. Here’s a detailed guide on some key text functions in Excel:

1. CHAR Function

• Description: Returns the character specified by a number from the character set.
• Syntax: =CHAR(number)
• Example: =CHAR(65) returns “A” (65 is the ASCII code for “A”).

2. CLEAN Function

• Description: Removes all non-printable characters from text.
• Syntax: =CLEAN(text)
• Example: =CLEAN(“Text with non-printable characters”) removes characters like line breaks.

3. TRIM Function

• Description: Removes extra spaces from text, leaving only single spaces between words.
• Syntax: =TRIM(text)
• Example: =TRIM(” Extra spaces “) returns “Extra spaces”.

4. CONCAT Function

• Description: Joins multiple text strings into one.
• Syntax: =CONCAT(text1, [text2], …)
• Example: =CONCAT(“Hello”, ” “, “World”) returns “Hello World”.

5. FIND Function

• Description: Finds the position of a substring within a text string, case-sensitive.
• Syntax: =FIND(find_text, within_text, [start_num])
• Example: =FIND(“o”, “Hello World”) returns 5.

6. SEARCH Function

• Description: Finds the position of a substring within a text string, case-insensitive.
• Syntax: =SEARCH(find_text, within_text, [start_num])
• Example: =SEARCH(“o”, “Hello World”) returns 5.

7. EXACT Function

• Description: Compares two text strings and returns TRUE if they are exactly the same, case-sensitive.
• Syntax: =EXACT(text1, text2)
• Example: =EXACT(“Hello”, “hello”) returns FALSE.

8. REPLACE Function

• Description: Replaces part of a text string with a different text string.
• Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
• Example: =REPLACE(“Hello World”, 7, 5, “Excel”) returns “Hello Excel”.

9. SUBSTITUTE Function

• Description: Replaces occurrences of a specified substring with another substring.
• Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Example: =SUBSTITUTE(“Hello World”, “World”, “Excel”) returns “Hello Excel”.

10. TEXT Function

• Description: Formats a number and converts it to text according to a specified format.
• Syntax: =TEXT(value, format_text)
• Example: =TEXT(1234.56, “$#,##0.00”) returns “$1,234.56”.

11. VALUE Function

• Description: Converts text that appears in a recognized format (like numbers) into a numeric value.
• Syntax: =VALUE(text)
• Example: =VALUE(“1234”) returns 1234.

12. TEXTJOIN Function

• Description: Joins text from multiple ranges and/or strings, with a delimiter between each text value.
• Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
• Example: =TEXTJOIN(“, “, TRUE, “Apple”, “Banana”, “Cherry”) returns “Apple, Banana, Cherry”.

13. REPT Function

• Description: Repeats text a specified number of times.
• Syntax: =REPT(text, number_times)
• Example: =REPT(“*”, 5) returns “*****”.

14. NUMBERVALUE Function

• Description: Converts text to a number, which can be useful for text that represents a numeric value.
• Syntax: =NUMBERVALUE(text)
• Example: =NUMBERVALUE(“1234.56”) returns 1234.56.

Text Format Table

Here’s a table showing how different date and time formats appear in Excel:

Practice Assignment

1. Create a Text Table:
   • Enter different text strings and apply each text function to manipulate and analyze the data.
2. Format Dates and Times:
   • Use the text format table to convert and display dates and times in various formats.
3. Analyze Results:
   • Verify the results by checking if the text manipulations meet the expected outcomes.

Pro Tip: To manage and manipulate text data effectively, understanding these functions is crucial. For a deeper dive into Excel’s capabilities and personalized guidance, Anjni Computer Education provides extensive resources and tutorials to enhance your skills.