Character Functions

Introduction

Character functions parse and format text.

Popular character functions include:

  • Length of a field (Length)
  • The position of a character or character set inside a field (Pos)
  • Showing only the first or last parts of a text field (Left, Right)
  • Deleting leading or ending blank spaces (Trim, LeftTrim, RightTrim)
  • Checking if a field contains a particular piece of text (Match)
  • Changing the upper/lower case of a field (WordCap, Upper, Lower)

Popular Character Functions

Length

Syntax: Length(string) where

  • "string" can be an object [Object_name] or text between quotation marks

Explanation: This function returns the number of characters in an object or text between quotation marks.

Examples:

=Length("no") would return 2, because the word "no" has two characters in it.

=Length([Employee_ID]) would return 7 for every single row, because Employee IDs are always seven numbers long.

Pos

Syntax: Pos(test_string;pattern) where

  • "test_string" can be an object [Object_name] or text between quotation marks
  • "pattern" can be an object [Object_name] or text between quotation marks.

Explanation: This function returns the position of a particular pattern of numbers and letters inside an object or text between quotation marks. It is particularly helpful in combination with other functions, such as Left and Right noted below.

Examples:

=Pos("How are you?";"?") would return 12, because the question mark appears in the twelfth position in the string.

=Pos("Santa Cruz";"Sa") would return 1, because the pattern starts on the first character.

=Pos([Employee_ID];"7") would return 1 for people at UCSC because 7 is the first digit of employee IDs here. Note that anyone from a different campus would have a different number returned based on where the first 7 actually appears, if it does at all. Note also that if someone has more than one 7 in their ID number, the position number returned will denote the very first instance of 7 only.

Left

Syntax: Left(string;number_of_characters) where

  • "string" can be an object [Object_name] or text between quotation marks
  • "number_of_characters" can be a simple number (e.g. 7) or a second function, such as Pos(), which will output a number.

Explanation: This function returns the first number of characters of a string, starting at the left.

Examples:

=Left("How are you?";5) would return "How a" because those are the first five characters of the string.

=Left([Employee_ID];"2") would return just the first two numbers of a person's employee ID.

Additional: LeftPad and LeftTrim offer options to add or subtract characters to the beginning of a text string.

Right

Syntax: Right(string;number_of_characters) where

  • "string" can be an object [Object_name] or text between quotation marks
  • "number_of_characters" can be a simple number (e.g. 7) or a second function, such as Pos(), which will output a number.

Explanation: This function returns the last number of characters of a string, starting at the right.

Examples:

=Right("How are you?";6) would return "e you?" because those are the last six characters of the string.

=Right([Employee_ID];"2") would return just the last two numbers of a person's employee ID.

Additional: RightPad offers the option to add or subtract characters to the beginning of a text string.

Trim

Syntax: Trim(string) where

  • "string" can be an object [Object_name] or text between quotation marks

Explanation: This function trims extra blanks from both sides of an object or string.

Example:

=Trim("    How are you?     ") would return "How are you?"

Additional: LeftTrim and RightTrim both trim the blanks from one side only.

Match

Syntax: Match(test_string;pattern) where

  • "test_string" can be an object [Object_name] or text between quotation marks
  • "pattern" can be an object [Object_name] or text between quotation marks

Explanation: This formula returns a "true" or "false" based on the presence or absence of the pattern in the test_string. "True" is denoted by the numeral 1, "False" by the numeral 0.

When the pattern is shorter than the test_string, you will have to use a wildcard as a placeholder for the extra characters on either side.

  • * is the wildcard for one or more characters
  • ? is the wildcard for one and only one character

Examples:

=Match("How are you?";"How are you?") would return 1 because those two strings match exactly.

=Match("How are you?";"are") would return 0 because the string and the pattern do not match exactly.

=Match("How are you?";"*are*") would return 1 because the string contains the pattern.

WordCap

Syntax: WordCap(string) where

  • "string" can be an object [Object_name] or text between quotation marks

Explanation: WordCap will capitalize the first letter of every word in your string.

Example:

=WordCap("how are you?") would return "How Are You?"

Additional: The functions Upper and Lower make all the letters in a string or object upper or lower case.