LearnExcel.io
Menu

Excel Text Functions

Written by ·
Excel Text Functions

Excel text functions are essential tools for anyone looking to manipulate data strings efficiently in spreadsheets. Let’s take a look at these powerful Excel functions for manipulating text.

Excel Functions – Text Functions

Excel Function

Description

[CHAR](/excel-char-function/)

Returns the character specified by a number code in the current character set.

[CLEAN](/excel-clean-function/)

Removes non-printable characters from text.

[CODE](/excel-code-function/)

Returns the numeric code for the first character in a text string. Useful for encoding text data.

[CONCAT](/excel-concat-function/)

Joins two or more text strings into one string. It’s an updated version of CONCATENATE.

[CONCATENATE](/excel-concatenate-function/)

Joins two or more text strings into one string. Useful for combining text, numbers, cell references, or a combination of these.

[EXACT](/excel-exact-function/)

Compares two text strings and returns TRUE if they are exactly the same.

[FIND](/excel-find-function/)

Locates a text string within another text string and returns the position where it starts. This function is case-sensitive.

[LEFT](/excel-left-function/)

Extracts a specified number of characters from the beginning of a text string.

[LEN](/excel-len-function/)

Returns the total number of characters in a specified text string.

[LOWER](/excel-lower-function/)

Converts all uppercase letters in a text string to lowercase. Does not affect numbers or special characters.

[MID](/excel-mid-function/)

Extracts a specific number of characters from any part of a text string, starting at the position you specify.

[PROPER](/excel-proper-function/)

Capitalizes the first letter of every word in a text string. Does not change numbers or special characters.

[REPLACE](/excel-replace-function/)

Replaces part of a text string with a different text string, based on the specified number of characters.

[REPT](/excel-rept-function/)

Repeats text a given number of times. Useful for creating repeated patterns or filling cells with repeated characters.

[RIGHT](/excel-right-function/)

Extracts a specified number of characters from the end of a text string.

[SEARCH](/excel-search-function/)

Similar to FIND, but this function is not case-sensitive. It locates one text string within another text string and returns the starting position.

[SUBSTITUTE](/excel-substitute-function/)

Replaces existing text with new text in a string. Unlike REPLACE, SUBSTITUTE can replace multiple occurrences of a specified text.

[TEXT](/excel-text-functions/)

Converts a value to text in a specified number format.

[TEXTJOIN](/excel-textjoin-function/)

Combines the text from multiple ranges and/or strings, and includes a delimiter you specify. Useful for creating lists or merging information.

[TRIM](/excel-trim-function/)

Removes extra spaces from text except for single spaces between words.

[UPPER](/excel-upper-function/)

Converts all lowercase letters in a text string to uppercase. Does not affect numbers or special characters.

[VALUE](/excel-value-function/)

Converts a text string that represents a number to a number.

Excel text functions are indispensable tools that help you manipulate, format, and analyze text data within your spreadsheets. Whether you’re merging data, extracting specific information, or formatting text entries for reports, understanding how to use these functions can significantly enhance your productivity with Excel.

Understanding Basic Text Functions

CONCATENATE and CONCAT

  • Purpose: Combine text from different cells into one cell.
  • Usage Example: =CONCATENATE(A1, " ", B1) or =CONCAT(A1, " ", B1)

LEFT, RIGHT, and MID

  • Purpose: Extract substrings from a text string.
  • Usage Example:
    • =LEFT(text, number_of_characters)
    • =RIGHT(text, number_of_characters)
    • =MID(text, start_position, number_of_characters)

LOWER, UPPER, and PROPER

  • Purpose: Change the case of text.
  • Usage Examples:
    • =LOWER("EXCEL") returns “excel”
    • =UPPER("excel") returns “EXCEL”
    • =PROPER("john doe") returns “John Doe”

Advanced Text Manipulation

  • Purpose: Locate the position of a text string within another text string.
  • Key Difference: FIND is case-sensitive, SEARCH is not.
  • Usage Example: =SEARCH("apple", A1), =FIND("Apple", A1)

REPLACE and SUBSTITUTE

  • Purpose: Replace text based on position or content.
  • Usage Example:
    • =REPLACE(old_text, start_num, num_chars, new_text)
    • =SUBSTITUTE(text, old_text, new_text, [instance_num])

Specialized Functions for Text Analysis

TRIM and CLEAN

  • Purpose: Remove extra spaces (TRIM) and non-printable characters (CLEAN) from text.
  • Usage Example: =TRIM(A1), =CLEAN(A1)

TEXT and VALUE

  • Purpose: Convert numbers to text and vice versa.
  • Usage Example: =TEXT(1234.567, "$#,##0.00"), =VALUE("1234")

TEXTJOIN

  • Purpose: Combine multiple ranges of text with a delimiter.
  • Usage Example: =TEXTJOIN(", ", TRUE, A1:A10)

Practical Applications and Examples

  • Data Cleaning: Using TRIM, CLEAN, and SUBSTITUTE to clean and format imported data.
  • Dynamic Text Generation: Creating dynamic labels and messages in dashboards.
  • Complex String Manipulation: Using a combination of MID, FIND, LEN, and SUBSTITUTE for advanced data parsing tasks.

Tips and Best Practices

  • Efficiency: Use CONCAT instead of CONCATENATE for better performance.
  • Error Handling: Wrap text functions with IFERROR when working with unpredictable data to avoid formula errors that can disrupt your workflow.

Conclusion

Mastering Excel text functions opens up a plethora of opportunities to refine data analysis and report generation. With practice, these functions will become an integral part of your Excel toolkit, helping you handle and transform textual data effortlessly.

Related guides

View all Excel Formulas and Functions guides →