Use the Munger Function

The following examples show uses of the Munger function. In these examples, assume that there is a character column of names with “Veronica Layman” as one of its values. To simplify the examples, the literal name “Veronica Layman” is the search string instead of a column name.

For instructions on how to incorporate Character functions, such as Munger, into a formula, see Character Functions in Formula Functions Reference.

Insert Characters

This Munger example finds the blank between the first and last name, and inserts the middle initial “J.” The formula Munger("Veronica Layman", 1, " ", " J. ") inserts the middle initial J., and evaluates as Veronica J. Layman.

Double quotation marks are required by the Munger function for literal strings, including strings that consist of a blank or when leading or trailing blanks are part of a string.

Delete Characters

To delete one or more characters from a string, follow these steps:

Designate the characters to delete as the Find string in the Munger function.

Enter an empty Replace string: two quotation marks with nothing between them.

For example, the function, Munger("Veronica Layman", 1, "onic", "") removes the “onic” from Veronica and evaluates as Vera Layman.

Note: A Replace field with a null (no value) string enclosed in quotation marks is different from a Replace field with no value. If you delete the Replace string altogether, Munger shows the argument name (“Replace”) in the Formula Editor window and behaves as if that optional argument does not exist. The resulting data type can also change from character to numeric, depending on the value of the Find/Length argument.

Find the Position (Index) of a Value

When the Find/Length field contains characters, Munger behaves like an index function and returns the numeric position of the first instance of the search string if it exists. For example, Munger("Veronica Layman", 1, " ") searches for a single blank and finds it in position nine. If the search string is not found, Munger returns a zero. This use of Munger produces the same result as the Contains function, as shown in Character Functions in Formula Functions Reference.

Find a Substring

Munger can extract substrings. For example, to extract only the first name, Munger("Veronica Layman", 1, 8,) starts at position one and reads through position eight. The remaining characters are ignored because the replace argument is not defined. This yields “Veronica” and produces the same result as the Substring, as shown in Character Functions in Formula Functions Reference.

An alternative way to find a substring is with a start value, any negative find value, and a no replace argument. Munger("Veronica Layman", 9, –1,) returns “Layman”.