Modify and cleanup your textual data via string formulas
There is a wide variety of formulas that will allow you to modify string values (texts) to the desired outcome.
Combine strings
If you would like to combine multiple fields in the mapping of a new field, you can use the following example to do so!
Example(s):
[Top level category] + ' > ' + [Low level category]
[EAN] + '-' + [Model ID]
FIRSTUPPERCASE
This function returns the input value with the first letter as capital.
Example(s):
FIRSTUPPERCASE(‘example’) = Example
REPLACE
This function allows you to replace a part of a string with another string.
Example(s):
REPLACE(‘This is a test’,’test’,’story’) = This is a story
TOSTRING
This function modifies a different value (e.g. number) to a string value.
Example(s):
TOSTRING(45)+'EUR' = 45EUR
TRIM
This function removes whitespace from both sides of a string.
Example(s):
TRIM(' Example ') = Example (without the whitespaces)
URLCODE
This function returns decoded or encoded URL according to the case.
Example(s):
URLCODE(‘http://testsite.com’,true) = http%3a%2f%2ftestsite.com
STRINGCASE
This function returns the string with the defined case format. You can select 4 options. All upper case, all lower case, the first letter of each word in upper case or only the first letter in upper case.
Example(s):
STRINGCASE(‘This is an example’,1) = THIS IS AN EXAMPLE
STRINGCASE(‘This is an example’,2) = this is an example
STRINGCASE(‘This is an example’,3) = This Is An Example
STRINGCASE(‘This is an example’,4) = This is an example
STRINGCLEAN
Cleans a text field by (a) removing special characters and (b) optionally replacing other characters.
Example(s):
Stringclean(‘Product& name% {version}’) = Product name version
Stringclean(‘Product\name\version’, ,’\’,’_‘) = Product_name_version
STRINGCLEAR
This function will clean data after/before sign. You can choose whether the sign in included or excluded as well, by setting the last digit to 1 (include) or 0 (exclude)
Example(s):
STRINGCLEAR(‘EX2001-BBB’,’-‘,0,1) = EX2001-
STRINGCLEAR(‘EX2001-BBB’,’-‘,1,1) = -BBB
STRINGCLEAR(‘EX2001-BBB’,’-‘,0,0) = EX2001
STRINGCLEAR(‘EX2001-BBB’,’-‘,1,0) = BBB
STRINGCUT
This function cuts a defined number of characters from the right or left side of a string.
Example(s):
STRINGCUT(‘EX2001Z23’,false,1) = EX2001Z2
STRINGCUT(‘EX2001Z23’,true,2) = 2001Z23
STRINGFIT
This function cuts a string to fit whole words according to a defined max length.
Example(s):
STRINGFIT(‘One example’,6) = One
STRINGLENGTH
This function returns the number of characters in a string.
Example(s):
STRINGLENGTH('Omnia') = 5
STRINGNULL
This function replaces a null or empty string with text.
Example(s):
STRINGNULL([Data-field],’Another value’), if the [Data-field] is empty, it will return ‘Another value’. If the Data field contains the a value (e.g. ‘Electronics’), it will return the value ‘Electronics)
STRINGPHRASE
This function divides a phrase into parts and returns the defined part. If there is no phrase at the indicated part this function will return an empty value (null).
Example(s):
STRINGPHRASE(Sound & Vision > Vision > Televisions,'>',1) = Sound & Vision
STRINGPHRASE(Sound & Vision > Vision > Televisions,'>',2) = Vision
STRINGPHRASE(Sound & Vision > Vision > Televisions,'>',3) = Televisions
STRINGSTRIPTAGS
This function removes all html tags from a string.
Example(s):
STRINGSTRIPTAGS(‘<p>This is an example</p>’, 1) = This is an example
SUBSTRING
This function returns the substring between two defined characters of a string.
Example(s):
SUBSTRING(‘This is an example’,1,4) = hi