Text functions

1. Extract

Reports may often contain concatenated values that need to be split. Use the Extract option to extract a string or character from a text field or split a text field.

You can choose different options, such as range, length, first/last character, or splitting the string based on a delimiter.

1.1. Range

Extracts text based on the start and end position of the string to be extracted. Let's extract the city code from the "AreaCode" column. We need to specify the starting position of the text to be extracted and the number of characters to extract.

Extract a range of data

The city code is extracted from the area code and captured as a separate dimension.

Extracted text using the range option

1.2. Length

Capture the text length in a separate dimension by choosing the Length option.

Extract text length for a selected field in the report

The length of the "Category" column has been extracted into a new dimension titled "Category length".

Length of the Category dimension extracted into a new field

1.3. First characters

Extract the first n characters from a text field. Let's pull the ID (first 5 characters) from the Customer ID field.

Customer ID field

Extract configuration to pull the first 5 characters from a field.

Extract ID from the Customer ID field

A new dimension containing the ID is created.

First 5 characters extracted into a new dimension

1.4. Last characters

Similar to the first characters option, Last Characters will pull the last n characters from a string.

1.5. Text before delimiters

Extracts the text before a specific delimiter. Let's extract the currency code from the "Discount Index" field.

Sample report to demonstrate the Extract function

Select the Text Before Delimiters option and enter "-" in the Delimiter text box.

Extract before delimiter option for delimited text

The currency code has been extracted into a separate dimension.

New dimension created with the extracted values

1.6. Text after delimiters

Extracts the text after a specific delimiter. Let's take the same example to extract the frequency from the Discount Index column. Select the Text After Delimiters option and enter "-" in the Delimiter text box.

Extracting strings after a delimiter

You'll see that a new dimension is created containing the text after the specified delimiter.

2. Format

Use the format option to perform operations like changing the case, trimming or adding a prefix/suffix to your text data.

Text formatting options

2.1. Changing the case

We may need to change the case of source data to ensure data consistency or search accuracy for case-sensitive systems. Let's change the case of the "Category" and "Segment" fields to uppercase.

Changing the case of text fields

Notice how the data in both fields is in uppercase.

Data after converting to uppercase

2.2. Trim

Leading or trailing spaces can cause mismatches in comparisons and can lead to inconsistencies in your data. The Account field has trailing spaces, so we'll use the Trim option to remove them.

Formatting text - trim spaces

The trailing spaces in the Account field have now been trimmed:

Trimmed data

2.3. Add prefix/suffix

Adding a prefix or suffix can help in easy identification and categorization of data. They are also used in generating unique identifiers. Let's add a prefix to the Account number.

Adding a prefix or suffix

You'll notice that the account numbers have an "ACC" prefix added.

Prefix added to the Account field