Text Functions#
If you are working with text in Spectrum then text functions help you determine information of the context of your text, such as seeing if the text contains certain words or characters, substituting strings with other strings, or returning a string as just lowercase letters.
Functions#
These are the text functions in Spectrum
CHAR#
Syntax#
CHAR(<number>)
Description#
Returns the character specified by the codepoint number in UTF-16 representation. This function uses decimal numbers and not hexadecimal.
Examples
| Column1 | CHAR returns |
|---|---|
| 65 | A |
| 115 | s |
| 220 | Ü (capital letter U with diaeresis) |
CODE#
Syntax#
CODE(<string>)
Description#
Returns the code point in decimal for the first character in a string.
Examples
| Column1 | CODE returns |
|---|---|
| A | 65 |
| Account | 65 |
| s | 115 |
| somewhere | 115 |
| Ü (capital letter U with diaeresis) | 220 |
CONCAT#
Syntax#
CONCAT(<any>; <any>; <any> ..)
Description#
Concatenates several items to one string item. Items in the function are separated by a semicolon.
Supports more than 255 arguments.
Any data type other than the LIST type can be used with the CONCAT function.
To add elements to a list, use the ADD function. To connect multiple lists into a single list, use the + operator.
Examples
Connecting columns
CONCAT(#Data!Column1;#Data!Column2)
| Column1 | Column2 | CONCAT returns |
|---|---|---|
| A | B | AB |
| Mark | Jones | MarkJones |
| Product | 6 | Product6 |
Adding specific values
CONCAT(#Data!Column1;"B")
| Column1 | Added value | CONCAT returns |
|---|---|---|
| 123 | "B" | 123B |
CONCAT(#Data!Column1;" ";"B")
| Column1 | Added value | Added value | CONCAT returns |
|---|---|---|---|
| 123 | " " | "B" | 123 B |
CONCAT(#Data!Column1;" ";#Data!Column2;" ";"B")
| Column1 | Added value | Column2 | Added value | Added value | CONCAT returns |
|---|---|---|---|---|---|
| 123 | " " | Product | " " | "B" | 123 Product B |
CONTAINS#
Syntax#
CONTAINS(<string or list>;<string>)
Description#
Returns a Boolean value if the string contains the desired characters (2nd argument) or not. This operation is case sensitive. CONTAINS returns false if one of the arguments is <null>.
Examples
| Column1 (string) | Contains | CONTAINS returns |
|---|---|---|
| United States | "united states" | false |
| United States | "United" | true |
| Germany | "Germany" | true |
| Germany | "ger" | false |
| United Kingdom | "King" | true |
| <null> | "King" | false |
| United Kingdom | <null> | false |
If the data you are using in your CONTAINS() is a list format data field, the string used to evaluate the element must match the entire element.
| Column1 (list) | Contains | CONTAINS returns |
|---|---|---|
| [United States, Germany] | "United" | false |
| [United States, Germany] | "United States" | true |
| [United States, Germany] | "germany" | false |
| [United States, Germany] | "Ger" | false |
| [United States, Germany] | "Germany" | true |
If you have a list data type and want to be able to use the CONTAIN() like a string, you can use TOJSON(), as in the following example.
| Column1 (list) | Contains | CONTAINS returns | Column1 TOJSON () | CONTAINS returns |
|---|---|---|---|---|
| [United States, Germany] | "United" | false |
["United States", "Germany"] | true |
| [United States, Germany] | "United States" | true |
["United States", "Germany"] | true |
| [United States, Germany] | "germany" | false |
["United States", "Germany"] | true |
| [United States, Germany] | "Ger" | false |
["United States", "Germany"] | true |
CONTAINS_IC#
Syntax#
CONTAINS(<string>;<string>)
Description#
Returns a Boolean value if the string contains the desired characters (2nd argument) or not. This operation isn't case sensitive, since IC stands for "ignore case". CONTAINS_IC returns false if one of the arguments is <null>.
Examples
| Column1 | Column2 | CONTAINS returns |
|---|---|---|
| United States | united states | true |
| United States | Unitet | false |
| Germany | Germany | true |
| Germany | ger | true |
| United Kingdom | Kind | false |
| null | Kind | false |
COUNTMATCHES#
Syntax#
COUNTMATCHES(<string>;<string with regex>)
Description#
Evaluates the regular expression against the column and returns the number of matches. The function is case sensitive.
Examples
| Column | Regular expression | COUNTMATCHES returns |
|---|---|---|
| Mississippi is a great state. | Mississippi | 1 |
| Mississippi | [^s] | 7 |
| Spectrum is making significant advances in big data analytics | Spectrum | advances | 2 |
| Here she hops on a horse in Honduras | h | 3 |
| Here she hops on a horse in Honduras | (?i)h | 5 |
COUNTTEXT#
Syntax#
COUNTTEXT(<string>;<string>)
Description#
Evaluates the substring against the column and returns the number of matches. The function is case sensitive.
Examples
| Column | Substring | COUNTTEXT returns |
|---|---|---|
| Mississippi is a great state. | Mississippi | 1 |
| Mississippi | ss | 2 |
| Spectrum is making significant advances in big data analytics | data | 1 |
| Here she hops on a horse in Honduras | h | 3 |
| Here she hops on a horse in Honduras | H | 2 |
ENDSWITH#
Syntax#
ENDSWITH(<string>;<string>)
Description#
Tests if this string ends with the specified suffix and returns a Boolean value.
Examples
| Column1 | Column2 | ENDSWITH returns |
|---|---|---|
| replace.txt | txt | true |
| This is a text | txt | false |
| This is a txt | txt | true |
ERRORMATCHES#
Syntax#
ERRORMATCHES(possibleError:<any>;regex:<string>)
Description#
- returns FALSE if 'possibleError' is not an ERROR value
- returns TRUE if the ERROR message of 'possibleError' matches the 'regex', returns FALSE otherwise
- 'regex' must be a constant and be valid, an invalid regex results in a column level error
Example
ERRORMATCHES (#B;'.failed.*')
| A | B | ERRORMATCHES |
|---|---|---|
| 0 | <error> | true |
| 1 | 5.0 | false |
| 2 | 2.5 | false |
| 3 | 1.6666 | false |
| 4 | 1.25 | false |
INDEX#
Syntax#
INDEX(<string>;<string with regex>)
Description#
Finds the first occurrence of the given regular expression or -1 if not found.
Spectrum tries to match entire record without automatically adding ^ or $ to the regular expression. If the record has multiple lines, then this must be accounted for with (?s) or line terminators.
Examples
| Column1 | Regex | INDEX returns |
|---|---|---|
| Hello Hannes | H[^e] | 6 |
| Hello Hannes | H[^ae] | -1 |
| Hello Hannes | H[^el] | 6 |
=INDEX("Hello Hannes"; "H[^e]") returns integer value 6
=INDEX("Hello Hannes"; "H[^ae]") returns integer value -1
JSON_ELEMENT#
Syntax#
JSON_ELEMENT(<string>; <number>)
Description#
Returns the given element of a JSON array. The first element of a JSON array is numbered 0.
Find more documentation on working with JSON here.
Examples
| Column1 | Number | JSON_ELEMENT returns |
|---|---|---|
| [a,b,null] | 0 | a |
| [a,b,null] | 0.0 | a |
| [a,b,null] | 1 | b |
| [a,b,null] | 2 | null |
JSON_ELEMENTS#
Syntax#
JSON_ELEMENTS(<string>;N)
Description#
Returns the top N elements of a JSON array.
If N is left blank, all elements of the JSON array are returned.
Find more documentation on working with JSON here.
Examples
| Column 1 | N | JSON_ELEMENTS returns |
|---|---|---|
| [a,b,null] | 1 | a |
| [a,b,null] | 2 | a, b |
| [a,b,null] | 3 | a, b, null |
JSON_KEYS#
Syntax#
JSON_KEYS(<JSON array as string>)
Description#
Returns all keys of a JSON object (key:value pairs of the JSON) as a list.
Find more documentation on working with JSON here.
Examples
| Column1 | JSON_KEYS returns |
|---|---|
| {"George":10, "Wins":[1,2,3]} | ["George", "Wins"] |
| {"First":"Joni", "Last":"Smith", "Age":10} | ["First", "Last", "Age"] |
| {"key1":25, "key2":75} | ["key1", "key2"] |
| {"Ultimate":4, "Answer":2} | ["Ultimate", "Answer"] |
JSON_MAP#
Syntax#
JSON_MAP(<string>; <string>)
Description#
Creates a JSON map from two JSON arrays.
Find more documentation on working with JSON here.
Example
=JSON_MAP(#Sheet1!keys;#Sheet1!values)
| Keys | Values | JSON_MAP returns |
|---|---|---|
| ["key1","key2"] | ["value1","value2"] | {"key1":"value1","key2":"value2"} |
| ["key1","key2","key3"] | ["value1","value2"] | {"key1":"value1","key2":"value2"} |
JSONARRAYSIZE#
Syntax#
JSONARRAYSIZE(<string containing a JSON array>)
Description#
Returns the number of elements of the selected JSON arrays.
Find more documentation on working with JSON here.
Examples
Given the following data:
| Key | Name |
|---|---|
| Group1 | Andy |
| Group1 | Alba |
| Group1 | Anna |
| Group1 | Affa |
| Group2 | Jeff |
| Group2 | June |
| Group2 | Jack |
| Group3 | Rick |
| Group3 | Rode |
| Group4 | Molly |
First create a group using GROUPBY(#RawData!Key).
| Group |
|---|
| Group1 |
| Group2 |
| Group3 |
| Group4 |
Next create a list with GROUPCONCAT(#RawData!Name).
| Group | Name_List |
|---|---|
| Group1 | [Andy, Alba, Anna, Affa] |
| Group2 | [Jeff, June, Jack] |
| Group3 | [Rick, Rode] |
| Group4 | [Molly] |
Use that list to create a JSON array with TOJSON(#sheet1!Name_List).
| Group | Name_List | Name_JSON |
|---|---|---|
| Group1 | [Andy, Alba, Anna, Affa] | ["Andy","Alba","Anna","Affa"] |
| Group2 | [Jeff, June, Jack] | ["Jeff","June","Jack"] |
| Group3 | [Rick, Rode] | ["Rick","Rode"] |
| Group4 | [Molly] | ["Molly"] |
Then use JSONARRAYSIZE(#Sheet1!Name_JSON), and the results are the number of elements in the corresponding JSON
| Group | Name_List | Name_JSON | JSONARRAYSIZE returns |
|---|---|---|---|
| Group1 | [Andy, Alba, Anna, Affa] | ["Andy","Alba","Anna","Affa"] | 4 |
| Group2 | [Jeff, June, Jack] | ["Jeff","June","Jack"] | 3 |
| Group3 | [Rick, Rode] | ["Rick","Rode"] | 2 |
| Group4 | [Molly] | ["Molly"] | 1 |
JSON_VALUE#
Syntax#
[JSON_VALUE<string>;<string containing json key>)
Description#
Returns a value for a given key from a JSON object.
Find more documentation on working with JSON here.
Examples
| Column1 | Key | JSON_VALUE returns |
|---|---|---|
| {"myBoolean":true,"myList":["a","b"],"myMap":{"c":"d","a":"b"},"myObject":"myClass","myJsonObject":{"myString":"myStringValue"}} | myBoolean | TRUE |
| {"myBoolean":true,"myList":["a","b"],"myMap":{"c":"d","a":"b"},"myObject":"myClass","myJsonObject":{"myString":"myStringValue"}} | myObject | myClass |
| {"myBoolean":true,"myList":["a","b"],"myMap":{"c":"d","a":"b"},"myObject":"myClass","myJsonObject":{"myString":"myStringValue"}} | myList | \["a","b"\] |
| {"myBoolean":true,"myList":["a","b"],"myMap":{"c":"d","a":"b"},"myObject":"myClass","myJsonObject":{"myString":"myStringValue"}} | myMap | \{"c":"d","a":"b"\} |
| {"myBoolean":true,"myList":["a","b"],"myMap":{"c":"d","a":"b"},"myObject":"myClass","myJsonObject":{"myString":"myStringValue"}} | myJsonObject | {"myString":"myStringValue"} |
JSONTOLIST#
Syntax#
qJSONTOLIST(
Description#
Converts the selected JSON data type column into a list data type column.
Find more documentation on working with JSON here.
Examples
| Column1 | JSONTOLIST returns |
|---|---|
| ["red","blue","yellow"] | [red, blue, yellow] |
| [red, blue, yellow] | [red, blue, yellow] |
| [red, blue, "yellow"] | [red, blue, yellow] |
| ["1","44","21","4"] | [1, 44, 21, 4] |
| [1, 44, 21, 4] | [1, 44, 21, 4] |
| [1, 44, "21", 4] | [1, 44, 21, 4] |
| [{"ID1":"1"},{ID2:2},{ID3:"3"}] | [{"ID1":"1"}, {"ID2":2}, {"ID3":"3"}] |
LEFT#
Syntax#
LEFT(<string>;<number>)
Description#
Returns the leftmost characters from a text value.
Examples
| Column1 | Number | LEFT returns |
|---|---|---|
| Hello Hannes | 3 | Hel |
| Spectrum | 5 | Datam |
| Good morning | 2 | Go |
LEN#
Syntax#
LEN(<string>)
Description#
Returns the number of characters in a text string.
Examples
| Column1 | LEN returns |
|---|---|
| Hello Hannes | 12 |
| Spectrum | 8 |
| Seven | 6 |
LOWER#
Syntax#
LOWER(<string>)
Description#
Converts text to lowercase
Examples
| Column1 | LOWER returns |
|---|---|
| TEST | test |
| Hello Hannes | hello hannes |
| teSt TWO | test two |
LTRIM#
Syntax#
LTRIM(<string>;\...)
Description#
Removes leading whitespaces or other characters from text. This function is case sensitive.
Examples
In this example, LTRIM has removed the leading white spaces from the specified column.
| Column1 | LTRIM(#Column1) returns |
|---|---|
| " Spectrum is wonderful" | "Spectrum is wonderful" |
In this example, LTRIM has additional added character values to remove from the leading string. LTRIM searches for both the character values "Datameer" and the blank space value " ".
| Column1 | LTRIM(#Column1;"Spectrum " ) returns |
|---|---|
| " Spectrum is wonderful" | "is wonderful" |
In this example, LTRIM has additional added character values to remove from the leading string. LTRIM searches only for the character values "Datameer". As the blank spaces are not specified and the values "Datameer" aren't leading from the string, no characters are removed.
| Column1 | LTRIM(#Column1;"wonderful" ) returns |
|---|---|
| " Spectrum is wonderful" | " Spectrum is wonderful" |
In this example, LTRIM has additional added character values to remove from the leading string. LTRIM searches for the character values "sironemaDt" and the blank space value " ". The blank spaces as well as the leading values are identified and removed. The order of the values added in the formula is not taken into account.
| Column1 | LTRIM(#Column1;" sironfulemaDt" ) returns |
|---|---|
| " Spectrum is wonderful" | " wonderful" |
MATCHES#
Syntax#
MATCHES(<string>,<string with regular expression>)
Description#
Returns a boolean value (true or false) depending on whether a string matches a regular expression. Spectrum tries to match entire record without automatically adding ^ or $ to the regular expression. But if the record has multiple lines, then this must be accounted for with (?s) or line terminators. The each cell must have a value and can't contain a
Examples
| Column1 | Column2 | MATCHES returns |
|---|---|---|
| nonemptylowercase | .[a-z]* | true |
| nonemptylowercase | .[A-Z]* | false |
| , | .[A-Z]* | false |
=MATCHES("nonemptylowercase", ".[a-z]*") returns boolean value true
=MATCHES("nonemptylowercase", ".[A-Z]*") returns boolean value false
=MATCHES("", ".[A-Z]*") returns boolean value false
NGRAM#
Syntax#
NGRAM(<string, list>;<number for length>;<number for length>;<number for length>;....)
Description#
Returns sub sequences of N characters of a given text. To see sub sequences of N words of a given text, refer to NGRAM_OLD. Spectrum tries to match entire record without automatically adding ^ or $ to the regular expression. But if the record has multiple lines, then this must be accounted for with (?s) or line terminators.
Example
| Data (string) |
|---|
| Spectrum is the best. |
NGRAM(#RawData!StringColumn;3)
| NGRAM() returns |
|---|
| Dat |
| ata |
| tam |
| ame |
| mee |
| eer |
| er |
| r i |
| is |
| is |
| s t |
| th |
| the |
| he |
| e b |
| be |
| bes |
| est |
| st. |
NGRAM_OLD#
Syntax#
NGRAM_OLD(<string>; <string regular expression for separator>; <number for length>; <number for length>; <number for length>;....)
Description#
Returns sub sequences of N words of a given text. To see subsequences of N characters of a given text, refer to NGRAM. Spectrum tries to match entire record without automatically adding ^ or $ to the regular expression. But if the record has multiple lines, then this must be accounted for with (?s) or line terminators.
Examples
| Data (string) |
|---|
| Spectrum is the best thing since sliced bread |
Example 1#
NGRAM_OLD(#RawData!StringColumn;" ";3)
| NGRAM_OLD() returns |
|---|
| Spectrum |
| Spectrum is |
| Spectrum is the |
| is the best |
| the best thing |
| best thing since |
| thing since sliced |
| since sliced bread |
| sliced bread |
| bread |
Example 2#
NGRAM_OLD(#RawData!StringColumn;" ";1;2;3)
| NGRAM_OLD() returns |
|---|
| Spectrum |
| is |
| the |
| best |
| thing |
| since |
| sliced |
| bread |
| Spectrum is |
| is the |
| the best |
| best thing |
| thing since |
| since sliced |
| sliced bread |
| Spectrum is the |
| is the best |
| the best thing |
| best thing since |
| thing since sliced |
| since sliced bread |
PROPER#
Syntax#
PROPER(
Description#
Capitalizes the first letter in each word of a text value.
Examples
| Column1 | PROPER returns |
|---|---|
| spectrum is cool | Spectrum is Cool |
| test | Test |
| good morning | Good Morning |
=PROPER(#Sheet1!B)returns each string with the first letter in each word capitalized
=PROPER("test") returns "Test"
REGEX#
Syntax#
REGEX(<source string>;<regular expression>;<replacement string>;<string used if the regular expression is not found>)
Description#
Using a regular expression, search through a string type column to section the text into groups. Define a replacement string for the result using the index (\$) and the sequential group number as a reference. For example, using \$1 will be replaced with the first defined group.
Learn more about:
- General information on regular expressions: https://docs.oracle.com/javase/tutorial/essential/regex/index
- How to capture groups using regular expressions: https://docs.oracle.com/javase/tutorial/essential/regex/groups.html
- Test regular expressions using a validator tool: http://www.regextester.com/
Formula Bar vs Formula Builder
There is a difference in expressions between the Formula Builder and the Formula Bar.
With the Formula Builder, if you are writing regular expressions, you can use normal syntax. A backslash (\) is used to separate each expression.
With the Formula Bar, if you are writing regular expressions you need to include an extra backslash (\) between each expression. This extra backslash between expressions is due to Spectrum using the backslash as an escape character.
Before using the REGEX function, think about using REPLACEALL or SUBSTITUTEALL for less complex actions. REGEX can be more process intensive and cause a degrade in performance.
Examples
| Source string | Regular expression | Replacement expression | REGEX returns |
|---|---|---|---|
| abc | [^b]*(b*)([^b]*) | \$2\$1 | cb |
| aaaabbbcs | [^b]*(b*)[^b]* | - \$1- | -bbb- |
| s | .*(b+).* | -\$1- | null |
| server.anywhere.com/usr/bin/amavisd | (^.*[/\\\\])?([^/\\\\]*)\$ | \$2 | server.anywhere.com/usr/bin |
| server.anywhere.com/usr/bin/amavisd | (^.*[/\\\\])?([^/\\\\]*)\$ | \$1 | amavisd |
REGEXTRACT#
Syntax#
REGEXTRACT(<string to be examined>;<regular expression>)
The regular expression can't reference a Workbook column.
Description#
Extracts tokens that match a regular expression. When using the REGEXTRACT function on a column from the same worksheet, the records from the source column are removed to match the function results.
Formula Bar vs Formula Builder
There is a difference in expressions between the Formula Builder and the Formula Bar.
With the Formula Builder, if you are writing regular expressions, you can use normal syntax. A backslash (\) is used to separate each expression.
With the Formula Bar, if you are writing regular expressions you need to include an extra backslash (\) between each expression. This extra backslash between expressions is due to Spectrum using the backslash as an escape character.
Examples
| Column1 | Regular expression | REGEXTRACT returns |
|---|---|---|
| hello world is out | \wo\w | hello, world, out |
| hello world is out | \w1\w | hello, world |
Twitter example: Extracting #hashtags and @mentions
| Column1 | Regular expression | REGEXTRACT returns |
|---|---|---|
| Hey, @Spectrum I love how you are so #awesome | \\@\\w+ | @Datameer |
| Hey, @Spectrum I love how you are so #awesome | \\#\\w+ | #awesome |
This function differs from the TOKENIZE function which returns tokens by separators that match the regular expression.
REGEXTRACT_LIST#
Syntax#
REGEXTRACT_LIST)(<string>;<token regex string>)
Description#
Using the ‘REGEXTTRACT_LIST’ Workbook function extracts tokens that match to a regular expression. This function complements the Workbook function ‘REGEXTRACT’.
‘REGEXTRACT_LIST’:
- does not hide records that do not contain a string that matched the given RegEx
- returns null in case a string has no RegEx match
- returns a list instead of a string
- if the string has a single RegEx match, the function will return a single element list
- if the string has multiple RegEx matches, the function will return them as list elements
- if the string has no RegEx match, the function will return an empty list
Examples
Given the following example string:
- [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
- [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop]
- [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
- [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix]
and the applied formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[tag:(.*?)\\]");"[tag:");"]")
Example Two Records RegEx Match
Formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[tgt:(.*?)\\]");"[tgt:");"]")
| String | Tokens |
|---|---|
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] | AffinityMix |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] | AffinityMix |
Example Four Records RegEx Match
Formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[adv:(.*?)\\]");"[adv:");"]")
| String | Tokens |
|---|---|
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | Colmar |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | Colmar |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] | Colmar |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] | Colmar |
Example No Records RegEx Match
Formula:
RTRIM(LTRIM(REGEXTRACT(#String;"\\[notag:(.*?)\\]");"[notag:");"]")
| String | Tokens |
|---|---|
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] | |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] | |
| [ini: SS21 ] [cou: DE][cou: DE] [fun: Hearts] [fmt: Banner] [opt: Brand Awareness][adv: Colmar] [ini: BAU] [chn: Display] [plt: DV360] [fun: Hearts] [opt: Brand Awareness] [cou: DE] [str: Affinity] [fmt: HalfPage] [dev: Desktop] [tgt: AffinityMix] |
REPLACE#
Syntax#
REPLACE(<string>;<number for start position>;<number of characters to replace>;<string replacement>)
Description#
Replaces characters within a text string with a different text string. Values less than zero for position or length cause an exception for actual data entry.
Examples
| String | Number of starting position | Number of characters to replace | String replacament | REPLACE returns |
|---|---|---|---|---|
| agfa | 1 | 2 | bbbb | abbbba |
| agfa | 1 | 10 | bb | abb |
| agfa | 2 | 1 | b | agba |
REPLACEALL#
Syntax#
REPLACEALL(<string>;<string with regular expression>;<string with replacement>)
Description#
Replaces each substring of this string that matches the given regular expression with the given replacement.
Spectrum tries to match entire record without automatically adding ^ or $ to the regular expression. But if the record has multiple lines, then this must be accounted for with (?s) or line terminators.
Formula Bar vs Formula Builder
There is a difference in expressions between the Formula Builder and the Formula Bar.
With the Formula Builder, if you are writing regular expressions, you can use normal syntax. A backslash (\) is used to separate each expression.
With the Formula Bar, if you are writing regular expressions you need to include an extra backslash (\) between each expression. This extra backslash between expressions is due to Spectrum using the backslash as an escape character.
Examples
| String | Regular expression | String with expression replacement | REPLACEALL returns |
|---|---|---|---|
| 2 years, two years | (2|two) | 3 | 3 years, 3 years |
| a b c | [ ]+ | - | a-b-c |
| a b c | [ ]+ | null | abc |
REPT#
Syntax#
REPT(<string>;<number>)
Description#
Repeats text a given number of times.
Examples
| Column1 | Number | REPT returns |
|---|---|---|
| no | 2 | nono |
| this is a test | 2 | this is a testthis is a test |
| Spectrum | 4 | SpectrumSpectrumSpectrumSpectrum |
RIGHT#
Syntax#
RIGHT(<string>;<number>)
Description#
Returns the rightmost characters from a text value.
Examples
| Column1 | Number | RIGHT returns |
|---|---|---|
| Programming is fun | 3 | fun |
| I love Spectrum | 2 | love |
| Spectrum is better than sliced bread | 5 | sliced |
RTRIM#
Syntax#
RTRIM(<string>;...)
Description#
Removes trailing whitespaces or other characters from text. The function is case sensitive.
Examples
In this example, RTRIM has removed the trailing white spaces from the specified column.
| Column1 | RTRIM(#Column1) returns |
|---|---|
| "Spectrum is wonderful " | "Spectrum is wonderful" |
In this example, RTRIM has additional added character values to remove from the trailing string. RTRIM searches for both the values "wonderful" and the blank space value " ".
| Column1 | RTRIM(#Column1;"wonderful " ) returns |
|---|---|
| "Spectrum is wonderful " | "Spectrum is" |
In this example, RTRIM has additional added character values to remove from the trailing string. RTRIM searches only for the values "wonderful". As the blank spaces are not specified and the values "wonderful" are not trailing from the string, no characters are removed.
| Column1 | RTRIM(#Column1;"wonderful" ) returns |
|---|---|
| "Spectrum is wonderful " | "Spectrum is wonderful " |
In this example, RTRIM has additional added character values to remove from the trailing string. RTRIM searches for the values "swoinrdelfu" and the blank space value " ". The blank spaces as well as the trailing values are identified and removed. The order of the values added in the formula is not taken into account.
| Column1 | RTRIM(#Column1;" swoinrdelfu" ) returns |
|---|---|
| "Spectrum is wonderful " | " Datam" |
SPLIT#
Syntax#
SPLIT(<string>;<string separator>;<index>)
Description#
Splits text specified by a string separator(s). An additional index value (with the index of 0 returning the first argument) can be assigned to return a specific split argument.
If you want to split or separate text using regular expression, use the TOKENIZE function.
Examples
Example 1:
Use the SPLIT function to separate a string column. Results are shown in a new column with all separated arguments in individual rows.
| Text | Separator |
|---|---|
| home/user/path1/path2/path3/file_name.csv | / |
Results
| Text | SPLIT_Result |
|---|---|
| home/user/path1/path2/path3/file_name.csv | home |
| home/user/path1/path2/path3/file_name.csv | user |
| home/user/path1/path2/path3/file_name.csv | path1 |
| home/user/path1/path2/path3/file_name.csv | path2 |
| home/user/path1/path2/path3/file_name.csv | path3 |
| home/user/path1/path2/path3/file_name.csv | file_name.csv |
Example 2:
Assign an index value to specify which arguments are returned.
| Text | Separator | Index |
|---|---|---|
| home/user/path1/path2/path3/file_name.csv | / | 5 |
Results
| Text | SPLIT_Result |
|---|---|
| home/user/path1/path2/path3/file_name.csv | file_name.csv |
Example 3:
If the string starts or ends with a separator, or has two consecutive separators, the SPLIT function produces empty results.
| Text | Separator |
|---|---|
| ,,x, | , |
Results
| Text | SPLIT_Result |
|---|---|
| ,,x, | |
| ,,x, | |
| ,,x, | x |
| ,,x, |
Example 4:
A limitation with the SPLIT function is that it is not possible to enter spaces within the formula builder as a string separator. It is still possible to enter spaces manually on the fx line in the Workbook.
Example:
| Text |
|---|
| Thomas Jackson |
| Emma Smith |
fx = SPLIT(#Text;" ";1)
Result
| Text | SPLIT_Result |
|---|---|
| Thomas Jackson | Jackson |
| Emma Smith | Smith |
STARTSWITH#
Syntax#
STARTSWITH(<string>;<string>)
Description#
Tests if this string starts with the specified prefix and returns a Boolean value. This command is case sensitive.
Examples
| String | Prefix | STARTSWITH returns |
|---|---|---|
| Spectrum is great | Da | true |
| Spectrum is great | Daf | false |
| Spectrum is great | Datam | true |
SUBSTITUTEALL#
Syntax#
SUBSTITUTEALL(<string>;<search string>;<new string>)
Description#
Substitutes new text for all occurrences of old text in a text string.
Examples
| String | Search string | New string | SUBSTITUTEALL returns |
|---|---|---|---|
| search | nomatch | replacement | search |
| search | search | replacement | replacement |
| search search | search | replacement | replacement replacement |
SUBSTITUTEFIRST#
Syntax#
SUBSTITUTEFIRST(<string>;<search string>;<new string>)
Description#
Substitutes new text for the first occurrence of the old text in a text string.
Examples
| String | Search string | New string | SUBSTITUTEFIRST returns |
|---|---|---|---|
| search | nomatch | replacement | search |
| search | search | replacement | replacement |
| search search | search | replacement | replacement search |
SUBSTR#
Syntax#
SUBSTR(<string>;<start number>;<length>)
Description#
Returns a specific number of characters from a text string starting at the position you specify. If no length is specified, the rest of the string is returned. The first value in a string has the start number of 0.
When a string value is evaluated by the function and no characters can be returned, the returned value is an empty string.
When a null value is evaluated by the function, the returned value is null.
Examples
=SUBSTR(#Sheet1!B;4;3) returns the next three characters in a string starting with the fifth character
=SUBSTR(null;0,1) returns null
| Column1 | Start number | SUBSTR returns |
|---|---|---|
| Admin-Annabelle | 6 | Annabelle |
| Admin-Mark | 6 | Mark |
| Admin-Lucy | 5 | -Lucy |
| Admin-Joshua | 6;4 | Josh |
| Admin-Joshua | 0;5 | Admin |
| Admin-Maria | 11 | |
| <null> | 6 | <null> |
T#
Syntax#
T(<any>)
Description#
Converts its arguments to text string data field type.
Examples
| Data | T returns |
|---|---|
| 7 | "7" |
| 23.6 | "23.6" |
| true | "true" |
| [apple, orange] | ["apple", "orange"] |
TOKENIZE#
Syntax#
TOKENIZE(<string>;<regular expression for separator>;<number>)
Description#
Tokenizes text by separators that match the regular expression. TOKENIZE can also return a specific token by specifying its index as optional 3rd argument. If the token of the specified index doesn't exist (because the index is out of range), TOKENIZE returns null. Because this function is meant for text analytics, it doesn't return a token for empty strings.
If you want to extract tokens that match a regular expression you need to use the REGEXTRACT function.
Examples
| String | Regular expression | Number | TOKENIZE returns |
|---|---|---|---|
| hello world | " " | null | "hello" "world" |
| hello world\t2 | \\W+ | null | "hello" "world" "2" |
| a.c,d | [.,] | null | "a" "c" "d" |
| 12-11-2006 | - | 1 | 11 |
| 12-11-2006 | - | 3 | null |
| "a.c,,d" | "[.,]" | null | "a" "c" ",d" |
| "a,,c,d" | "," | null | "a" ",c" "d" |
TRIM#
Syntax#
TRIM(<string>)
Description#
Returns a copy of the string, with leading and trailing whitespace omitted.
Examples
| Column1 | TRIM returns |
|---|---|
| This is a test | This is a test |
| Spectrum | Spectrum |
| I love data | I love data |
UPPER#
Syntax#
UPPER(<string>)
Description#
Converts text to uppercase.
Examples
| Column1 | UPPER returns |
|---|---|
| Spectrum is the best | DATAMEER IS THE BEST |
| test | TEST |
| Big Data Is Awesome | BIG DATA IS AWESOME |
=UPPER(#Sheet1!B)returns each string as uppercase.
=UPPER("test") returns "TEST"