Text Formulas

 

1. CONCATENATE:

   • Syntax: =CONCATENATE(text1, text2, ...)
   • Example: =CONCATENATE(A1, " ", B1)
   • Combines multiple text strings into one.

2. 
   

3. LEN (Length):

   • Syntax: =LEN(text)
   • Example: =LEN(A1)
   • Returns the number of characters in a text string.
   • 
     

4. LEFT:

   • Syntax: =LEFT(text, num_chars)
   • Example: =LEFT(A1, 3)
   • Returns a specified number of characters from the beginning of a text string.
   • 
     

5. RIGHT:

   • Syntax: =RIGHT(text, num_chars)
   • Example: =RIGHT(A1, 3)
   • Returns a specified number of characters from the end of a text string.
   • 
     

6. MID:

   • Syntax: =MID(text, start_num, num_chars)
   • Example: =MID(A1, 2, 3)
   • Returns a specific number of characters from a text string, starting at the specified
   • position.
   • 
     

7. LOWER:

   • Syntax: =LOWER(text)
   • Example: =LOWER(A1)
   • Converts text to lowercase.
   • 
     

8. UPPER:

   • Syntax: =UPPER(text)
   • Example: =UPPER(A1)
   • Converts text to uppercase.
   • 
     

9. PROPER:

   • Syntax: =PROPER(text)
   • Example: =PROPER(A1)
   • Capitalizes the first letter of each word in a text string.
   • 
     

10. TRIM:

    • Syntax: =TRIM(text)
    • Example: =TRIM(A1)
    • Removes leading and trailing spaces from a text string.
    • 
      

11. SUBSTITUTE:

• Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Example: =SUBSTITUTE(A1, "apple", "orange")
• Replaces occurrences of a specified substring with another substring.

11. • Removes non-printable characters from text.
    • 
      

12. EXACT:

    • Syntax: =EXACT(text1, text2)
    • Example: =EXACT(A1, B1)
    • Checks if two text strings are exactly the same.
    • 
      

13. VALUE:

    • Syntax: =VALUE(text)
    • Example: =VALUE(A1)
    • Converts a text string that represents a number to a numeric value.
    • 
      

14. LEFTB and RIGHTB:

    • Syntax: =LEFTB(text, num_bytes) and =RIGHTB(text, num_bytes)
    • Example: =LEFTB(A1, 2)
    • Returns a specified number of bytes from the beginning or end of a text string (useful for double-byte character sets).
    • 
      

15. MIDB:

• Syntax: =MIDB(text, start_num, num_bytes)
• Example: =MIDB(A1, 2, 3)
• Returns a specific number of bytes from a text string, starting at the specified position (useful for double-byte character sets).
• 
  

11. FIND:

    • Syntax: =FIND(find_text, within_text, [start_num])
    • Example: =FIND("e", A1)
    • Returns the starting position of a text string within another text string.
    • 
      

12. SEARCH:

    • Syntax: =SEARCH(find_text, within_text, [start_num])
    • Example: =SEARCH("e", A1)
    • Similar to FIND but is not case-sensitive.
    • 
      

13. REPLACE:

    • Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
    • Example: =REPLACE(A1, 2, 3, "XYZ")
    • Replaces part of a text string with another text string.
    • 
      

14. TEXT:

    • Syntax: =TEXT(value, format_text)
    • Example: =TEXT(NOW(), "dd-mmm-yyyy")
    • Converts a value to text with a specified format.

15. CONCAT:

    • Syntax: =CONCAT(range)
    • Example: =CONCAT(A1:A10)
    • Concatenates the text in a range.
    • 
      

16. LEFTB, RIGHTB, MIDB:

    • Similar to LEFT, RIGHT, MID, but designed for double-byte character set languages.
    • 
      

17. EXACT:

    • Syntax: =EXACT(text1, text2)
    • Example: =EXACT(A1, B1)
    • Tests whether two text strings are identical.
    • 
      

18. CLEAN:

    • Syntax: =CLEAN(text)
    • Example: =CLEAN(A1)
    • Removes non-printable characters from text.
    • 
      

19. CONCATENATEX:

    • Syntax: =CONCATENATEX(range, delimiter)
    • Example: =CONCATENATEX(A1:A10, ", ")
    • Concatenates values with a specified delimiter.
    • 
      

20. LEFTB, RIGHTB, MIDB:

• Similar to LEFT, RIGHT, MID, but designed for double-byte character set languages.

21. CLEAN:

    • Syntax: =CLEAN(text)
    • Example: =CLEAN(A1)
    • Removes non-printable characters from text.
    • 
      

22. VALUE:

    • Syntax: =VALUE(text)
    • Example: =VALUE(A1)
    • Converts a text string that represents a number to a number.
    • 
      

23. CODE:

    • Syntax: =CODE(text)
    • Example: =CODE("A")
    • Returns the ASCII value of the first character in a text string.
    • 
      

24. CHAR:

    • Syntax: =CHAR(number)
    • Example: =CHAR(65)
    • Returns the character specified by a number (ASCII value).
    • 
      

25. UNICHAR:

    • Syntax: =UNICHAR(number)
    • Example: =UNICHAR(128516)
    • Returns the Unicode character based on the specified number.
    • 
      

26. UNICODE:

    • Syntax: =UNICODE(text)
    • Example: =UNICODE("A")
    • Returns the Unicode value of the first character in a text string.
    • 
      

27. LEFT, RIGHT, MID with FIND/SEARCH:

    • Combining these functions with FIND or SEARCH allows more dynamic text extraction based on specific criteria.
    • 
      

28. TEXTJOIN:

    • Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
    • Example: =TEXTJOIN(", ", TRUE, A1:A10)
    • Concatenates a list or range of text strings using a delimiter.
    • 
      

29. SPLIT:

    • Requires using Text to Columns or a combination of formulas to split text based on a specified delimiter.
    • 
      

30. SUBTOTAL:

• Syntax: =SUBTOTAL(function_num, range1, range2, ...)
• Example: =SUBTOTAL(101, A1:A10)
• Performs a specified calculation (such as sum or average) on a filtered range.

31. TEXTVALUE:

    • Requires using a combination of functions to convert a text representation of a date or number to a true date or number value.
    • 
      

32. TEXTSPLIT:

    • Requires using formulas like MID, SEARCH, and LEN to split text based on a specific criterion.
    • 
      

33. SUBSTITUTE with TEXTJOIN:

    • Syntax: =TEXTJOIN(delimiter, TRUE, SUBSTITUTE(range, old_text, new_text))
    • Example: =TEXTJOIN(", ", TRUE, SUBSTITUTE(A1:A10, " ", "_"))
    • Replaces spaces with underscores in each cell of a range and then joins them with a delimiter.
    • 
      

34. LEFT, RIGHT, MID with LEN:

    • Combining these functions with LEN allows dynamic extraction of text based on the length of the text.
    • 
      

35. CONCATENATE with LINE BREAKS:

    • Syntax: =CONCATENATE(A1, CHAR(10), B1)
    • Example: Concatenates text in cell A1 and B1 with a line break.
    • 
      

36. HYPERLINK:

    • Syntax: =HYPERLINK(link_location, friendly_name)
    • Example: =HYPERLINK("https://www.example.com", "Visit Website")
    • Creates a clickable hyperlink.
    • 
      

37. CLEAN with SUBSTITUTE:

    • Syntax: =CLEAN(SUBSTITUTE(A1, CHAR(10), ""))
    • Example: Removes line breaks from text using CLEAN and SUBSTITUTE.
    • 
      

38. TEXT with DATE Function:

    • Syntax: =TEXT(DATE(year, month, day), "mm/dd/yyyy")
    • Example: Converts a date to a specific text format.
    • 
      

39. CONCAT with IF and ISNUMBER:

    • Syntax: =CONCAT(IF(ISNUMBER(range), range, ""))
    • Example: Concatenates only numeric values from a range.
    • 
      

40. CONCAT with IFERROR:

• Syntax: =CONCAT(IFERROR(range, ""))
• Example: Concatenates values, ignoring errors.
• 
  

41. TEXT with NOW Function:

    • Syntax: =TEXT(NOW(), "mm/dd/yyyy hh:mm AM/PM")
    • Example: Formats the current date and time as text.
    • 
      

42. SEARCH and SUBSTITUTE Combo:

    • Syntax: =SEARCH("search_text", A1)
    • Syntax: =SUBSTITUTE(A1, "old_text", "new_text")
    • Example: Use SEARCH to find the position of a specific text and SUBSTITUTE to replace it.
    • 
      

43. VALUE with IFERROR:

    • Syntax: =VALUE(IFERROR(A1, ""))
    • Example: Converts text to a numeric value, handling errors gracefully.
    • 
      

44. LEN with SUBSTITUTE:

    • Syntax: =LEN(SUBSTITUTE(A1, " ", ""))
    • Example: Counts the number of characters excluding spaces.
    • 
      

45. JOIN with ARRAYFORMULA (for Google Sheets):

    • Syntax: =JOIN(", ", ARRAYFORMULA(A1:A10))
    • Example: Concatenates a range of cells with a delimiter in Google Sheets.
    • 
      

46. TEXT with CONCATENATE:

    • Syntax: =TEXT(CONCATENATE(A1, B1), "yyyy-mm-dd")
    • Example: Converts concatenated text to a specific date format.
    • 
      

47. PROPER with TRIM:

    • Syntax: =PROPER(TRIM(A1))
    • Example: Capitalizes the first letter of each word and removes leading/trailing spaces.
    • 
      

48. EXACT with LOWER or UPPER:

    • Syntax: =EXACT(LOWER(A1), LOWER(B1))
    • Example: Compares two text strings case-insensitively.
    • 
      

49. TEXT with VLOOKUP:

    • Syntax: =TEXT(VLOOKUP(lookup_value, table_array, col_index_num, FALSE), "mm/dd/yyyy")
    • Example: Retrieves a date value using VLOOKUP and formats it.
    • 
      

50. FILTER with TEXT:

• Syntax: =TEXT(FILTER(A1:A10, B1:B10="Criteria"), "mm/dd/yyyy")
• Example: Filters dates based on a specific criteria and formats them.
• 
  
51. TEXT with MID and SEARCH:
• Syntax: =TEXT(MID(A1, SEARCH("start", A1) + 5, 10), "mm/dd/yyyy")
• Example: Extracts a substring from a text string using MID and SEARCH, then formats it as a date.
52. IF with ISNUMBER:
• Syntax: =IF(ISNUMBER(FIND("text", A1)), "Found", "Not Found")
• Example: Checks if a specific text is present in a cell.
53. TEXT with CONCATENATE and IF:
• Syntax: =TEXT(CONCATENATE(A1, B1), "yyyy-mm-dd")
• Example: Concatenates two text cells and converts them to a specific date format.
54. LEFT with FIND/SEARCH and LEN:
• Syntax: =LEFT(A1, FIND(" ", A1)-1)
• Example: Extracts the first word from a text string.
55. REPT:
• Syntax: =REPT(text, number_of_times)
• Example: =REPT("ABC", 3) repeats the text "ABC" three times.
56. SEARCH with ARRAYFORMULA (for Google Sheets):
• Syntax: =ARRAYFORMULA(SEARCH("text", A1:A10))
• Example: Searches for a specific text in a range of cells in Google Sheets.
57. TEXT with LEFT and LEN for Conditional Formatting:
• Syntax: =LEN(LEFT(A1, 5))=5
• Example: Uses LEN and LEFT to conditionally format cells with a specific length.
58. TEXTJOIN with IF:
• Syntax: =TEXTJOIN(", ", TRUE, IF(condition, A1:A10, ""))
• Example: Joins text based on a condition.
59. HYPERLINK with CONCATENATE:
• Syntax: =HYPERLINK(CONCATENATE("https://www.example.com/", A1), A1)
• Example: Creates hyperlinks based on text in a cell.
60. UNION with Power Query (Get & Transform):
• Combines data from multiple text files using Power Query in Excel.

These text formulas can be very useful for manipulating and analyzing text data in Excel.