Back to Function Index

TRANSLATE()

Overview

TRANSLATE() performs character-by-character translation of a string by replacing each character in the search list with its corresponding character in the replace list. If a character in the search list has no corresponding character in the replace list, it is removed from the string. This function is case sensitive, making it useful for precise character manipulation and string transformation.

Function Format

TRANSLATE(string, search list, replace list)

Return Value

TRANSLATE() returns a character string

Examples

// Case conversion with partial replacement
TRANSLATE("APPLES", "PLES", "ples") = "Apples"

// Character removal
TRANSLATE("APPLES AND ORANGES", "S", "") = "APPLE AND ORANGE"

// Complex character mapping
TRANSLATE("APPLES AND ORANGES", "DLGORAPESN ", "RFTUI") = "FRUIT"

// Additional examples
TRANSLATE("HELLO", "LO", "lo") = "HEllo"     // Partial case conversion
TRANSLATE("12345", "123", "ABC") = "ABC45"   // Number to letter conversion
TRANSLATE("ABC", "ABC", "XYZ") = "XYZ"       // Complete replacement

Notes

• Important behaviors:
  • Case sensitive operation
  • Character-by-character translation
  • Removes characters without replacements
  • Preserves characters not in search list
  • Maintains original string length if no removals
• Common use cases include:
  • Case conversion
  • Character removal
  • String normalization
  • Data cleaning
  • Text transformation
• The function is useful for:
  • Text formatting
  • Data standardization
  • String manipulation
  • Character mapping
• Important considerations:
  • Search and replace lists must be character strings
  • Order of characters in lists matters
  • Empty replace list removes all search characters
  • No effect on characters not in search list

See Also

• REPLACE()
• LOWER()
• UPPER()
• TRIM()