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.
