Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create One and Two Variable Data Tables in Excel
  • How to create a Histogram in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Move chart to a separate worksheet in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

References

  • Approximate match with multiple criteria in Excel
  • How to get relative row numbers in a range in Excel
  • How to get last column number in range in Excel
  • How to retrieve first match between two ranges in Excel
  • Find closest match in Excel

Data Validations

  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only

Complete List of Excel Text Functions References and Examples

by

Excel has many functions to offer when it comes to manipulating text strings.

The Excel TEXT function returns a number in a specified number format, as text. You can use the TEXT function to embed formatted numbers inside text.

Here’s a full list of Built-In Text Functions in Excel and their Descriptions below:

Click each of the function link to see detailed example.

Functions to Remove Extra Characters

CLEAN Removes all non-printable characters from a supplied text string
TRIM Removes duplicate spaces, and spaces at the start and end of a text string

Functions to Convert Between Upper & Lower Case

LOWER Converts all characters in a supplied text string to lower case
PROPER Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case)
UPPER Converts all characters in a supplied text string to upper case

Functions to Convert Excel Data Types

BAHTTEXT Converts a number, plus the suffix “Baht” into Thai text
DOLLAR Converts a supplied number into text, using a currency format
FIXED Rounds a supplied number to a specified number of decimal places, and then converts this into text
TEXT Converts a supplied value into text, using a user-specified format
VALUE Converts a text string into a numeric value
NUMBERVALUE Converts text to a number, in a locale-independent way (New in Excel 2013)

Converting Between Characters & Numeric Codes

CHAR Returns the character that corresponds to a supplied numeric value
CODE Returns the numeric code for the first character of a supplied string
UNICHAR Returns the Unicode character that is referenced by the given numeric value (New in Excel 2013)
UNICODE Returns the number (code point) corresponding to the first character of a supplied text string (New in Excel 2013)

Cutting Up & Piecing Together Text Strings

CONCAT Joins together two or more text strings (New in Excel 2016 (not Excel 2016 for Mac) – replaces the Concatenate function)
CONCATENATE Joins together two or more text strings (Replaced by Concat function in Excel 2016)
LEFT Returns a specified number of characters from the start of a supplied text string
MID Returns a specified number of characters from the middle of a supplied text string
RIGHT Returns a specified number of characters from the end of a supplied text string
REPT Returns a string consisting of a supplied text string, repeated a specified number of times
TEXTJOIN Joins together two or more text strings, separated by a delimiter (New in Excel 2016 – not available in Excel 2016 for Mac)

Information Functions

LEN Returns the length of a supplied text string
FIND Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)
SEARCH Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)
EXACT Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive)
T Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

Replacing / Substituting Parts of a Text String

REPLACE Replaces all or part of a text string with another string (from a user supplied position)
SUBSTITUTE Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text

 

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Basic timesheet formula with breaks in Excel
  • How to calculate future date say 6 months ahead in Excel
  • Get project end date in Excel
  • Calculate time difference in hours as decimal value in Excel

Grouping

  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel
  • Map inputs to arbitrary values in Excel

General

  • Count cells that do not contain many strings in Excel
  • How to get original number from percent change in Excel
  • Count cells that contain errors in Excel
  • Convert column letter to number in Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning