Skip to content

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 value. Update columns containing values using functions like DENULLIFY or ISNULL.

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:

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:

  1. [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]
  2. [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]
  3. [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]
  4. [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
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"