Configure the Substring Function

This task describes how to configure the substring function, to copy part of a string in your data model from one column to another.

You configure the substring function in the Substring Dialog Box. For user interface details, see Substring Dialog Box.

To configure the substring;

Create a format string to define which part of the data in the source column you want to copy.

The format string can consist of any combination of the following:

Format Description Example
{n} Copies the n-th character from the source string.

Format string: {3}

Source string: abcde

Substring result: c
{n+} Copies all characters from the n-th character to the end of the source string.

Format string: {5+}

Source string: abcdefgh

Substring result: efgh
{n-m} where n is located before m in the string

Copies all characters from the n-th character to the m-th character (inclusive).

Format string: {2-6}

Source string: abcdefgh

Substring result: bcdef
{m-n} where n is located before m in the string Copies all characters from the m-th character, backwards to the n-th character (inclusive).

Format string: {6-2}

Source string: abcdefgh

Substring result: fedcb
{n:m}

Copies m characters, starting from the n-th character.

If there are not enough characters in the source string, a space is added for each missing character.

Format string: {7:2)

Source string: abcdefgh

Substring result: gh
Regular text

You can add additional text The text you enter is copied as is.

Limitation: You cannot use the characters { } or \. They are used to mark special format sequences.

Workaround: To use the characters { } or \ as part of a text string you enter, each of these characters must be preceded by the backslash character \. Placing a backslash before one of these special characters overrides their special meaning.

Format: Text with backslash \\ and curly brackets \{ \}

Result: Text with backslash \ and curly brackets { }

Example:  

In the Format field of the Substring dialog box, you enter the following format string:

All characters:{1+}. Only 6th and 8th characters: {6}{8}. Result of \{5:5\} is: {5:5}. First 13 characters reversed: {13-1}.

The source string is: 123456789ABCDEF

The result, output to the cell configured with the substring function is:

All characters:123456789ABCDEF. Only 6th and 8th characters: 68. Result of {5:5} is: 56789. First 13 characters reversed: DCBA987654321.

See also: