Table of Contents
Additional Custom Functions
This page details the additional custom functions available in DQ for Excel.
DQ.CASE_STRING
Cases a string.
Parameters:
input (string), text to case
caseType (string), type of case, from list:
- ProperCase_FamilyName
- ProperCase_Address
- UpperCase
- LowerCase
- TitleCase
language (string), language of input, from list:
- English
- Spanish
- French
- Italian
- German
returns (string), cased string
Examples:
| input | caseType | language | output |
|---|---|---|---|
| someTEXT | uppercase | english | SOMETEXT |
| someTEXT | lowercase | english | sometext |
DQ.IS_ALL_UPPER
Checks if string is made up of only upper case characters.
Parameters:
input (string), text to check
returns (boolean), true if all upper case
| input | output |
|---|---|
| SOMETEXT | TRUE |
| someTEXT | FALSE |
DQ.IS_ALL_LOWER
Checks if a string is made up of only lower case characters
Parameters:
input (string), text to check
returns (boolean), true if all lower case
| input | output |
|---|---|
| sometext | TRUE |
| someTEXT | FALSE |
DQ.IS_MIXED_CASE
Checks if a string is made up of mixed case characters.
Parameters:
input (string), text to check
returns (boolean), true if mixed case
| input | output |
|---|---|
| someTEXT | TRUE |
| sometext | FALSE |
DQ.IS_ALPHA_NUMERIC
Checks if a string is made up of alpha numeric characters.
Parameters:
input (string), text to check
returns (boolean), true if alpha numeric
| input | output |
|---|---|
| s0meT3XT | TRUE |
| s!m?T3XT | FALSE |
DQ.IS_NUMERIC
Checks if a string is made up of numeric characters.
Parameters:
input (string), text to check
returns (boolean), true if numeric
| input | output |
|---|---|
| 12345678 | TRUE |
| someTEXT | FALSE |
DQ.IS_ISO4217_CURRENCY_CODE
Checks if a string is a ISO 4217 currency code. See ISO-4127-currency-codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO 4217 currency code
| input | output |
|---|---|
| EUR | TRUE |
| EURO | FALSE |
DQ.IS_ISO2_CODE
Checks if a string is an ISO 3166-1 alpha-2 country code. See ISO 3166 country codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO2 code
| input | output |
|---|---|
| GB | TRUE |
| GBR | FALSE |
DQ.IS_ISO3_CODE
Checks if a string is an ISO 3166-1 alpha-3 country code. See ISO 3166 country codes for more information.
Parameters:
input (string), text to check
returns (boolean), true if ISO3 code
| input | output |
|---|---|
| GBR | TRUE |
| GB | FALSE |
DQ.REMOVE_LEADING
Removes a value from the start of a string.
Parameters:
input (string), text to modify
valueToRemove (string), value to remove from text
leaveOneAtStart (boolean), if true leave one occurrence at the start of the string
returns (string) modified text
| input | valueToRemove | leaveOneAtStart | output |
|---|---|---|---|
| mymymymymyTestString | my | TRUE | myTestString |
| mymymymymyTestString | my | FALSE | TestString |
DQ.REMOVE_CHARACTERS
Removes a type of character from a string.
Parameters:
input (string), text to modify
characterType (string), type of character, from list:
- Digit
- Letter
- LetterOrDigit
- Punctuation
- Whitespace
- Upper
- Lower
- Symbol
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- OddDigit
- EvenDigit
- NonPrinting
leaveOneAtStart (boolean), if true leave one occurrence at the start of the string
returns (string), modified text
| input | characterType | leaveOneAtStart | output |
|---|---|---|---|
| Te123st | Digit | FALSE | Test |
| Te123st | Letter | FALSE | 123 |
| Te12££st | LetterOrDigit | FALSE | ££ |
| Te.st | Punctuation | FALSE | Test |
| Te st | Whitespace | FALSE | Test |
| teEst | Upper | FALSE | Test |
| TEeST | Lower | FALSE | Test |
| Te$$st | Symbol | FALSE | Test |
| TeEst | UpperCaseVowel | FALSE | Test |
| TeEst | LowerCaseVowel | FALSE | TEst |
| teTst | UpperCaseConsonant | FALSE | test |
| TetST | LowerCaseConsonant | FALSE | TeST |
| Te1st | OddDigit | FALSE | Test |
| Te2st | EvenDigit | FALSE | Test |
| Test | NonPrinting | FALSE | Test |
DQ.REMOVE_SINGLE_CHARACTER_WORDS
Removes single character words from a string.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| this is a very long string I think | this is very long string think |
DQ.REPLACE_ADJACENT_REPEATING_TEXT
Replaces adjacent repeating text.
Parameters:
input (string), text to modify
repeatingValue (string), value to find and replace
replacement (string), replace value
returns (string), modified text
| input | repeatingValue | replacement | output |
|---|---|---|---|
| exaaaaaaaample aababaaa | a | @ | exa@@@@@@@mple a@baba@@ |
DQ.REPLACE_IF_ENDS_WITH
Replaces a piece of text at the end of a string if found.
Parameters:
input (string), text to modify
repeatingValue (string), substring to replace, at the end of string
replacement (string), replace value
returns (string), modified text
| input | repeatingValue | replacement | output |
|---|---|---|---|
| this is an input | put | ?? | this is an in?? |
DQ.REPLACE_IF_STARTS_WITH
Replaces a piece of text at the start of a string if found.
Parameters:
input (string), text to modify
repeatingValue (string), substring to replace, at the start of string
replacement (string), replace value
returns (string), modified text
| input | repeatingValue | replacement | output |
|---|---|---|---|
| this is an input | th | ?? | ??is is an input |
DQ.STRING_TO_BINARY
Converts a string to binary.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| test | 01110100011001010111001101110100 |
DQ.BINARY_TO_STRING
Converts a binary code into a string
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| 01110100011001010111001101110100 | test |
DQ.STRING_TO_HEX
Converts a string to hexadecimal.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| test | 74657374 |
DQ.HEX_TO_STRING
Converts a hexadecimal string to binary.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| 74657374 | test |
DQ.REVERSE
Reverse a given string.
Parameters:
input (string), text to reverse
returns (string), reversed text
| input | output |
|---|---|
| myString | gnirtSym |
DQ.NORMALIZE_WHITE_SPACE
Normalizes white space in a string by collapsing white space into single space characters.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| my—string—-with—-weird-white–space | my string with weird white space |
Note The values in the input column use dashes (-) to represent spaces.
DQ.NORMALIZE_ALPHA_NUMERIC_PHONE
Normalizes alpha numeric characters in a given phone number.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| 1-800-flowers | 1-800-3569377 |
DQ.COLLAPSE_ADJACENT_REPEATED_CHARACTERS
Collapses adjacent repeated characters.
Parameters:
input (string), text to modify
collapseNumerics (boolean), true if numbers should be collapsed
maximumRepeat (number), the number of characters allowed before collapsing
returns (string), modified text
| input | collapseNumerics | maximumRepeat | output |
|---|---|---|---|
| Silly | Sily | ||
| Silly4455667777788890 | TRUE | Sily4567890 | |
| Silly4455667777788890 | FALSE | Sily4455667777788890 | |
| Sillllllly | FALSE | 1 | Sillllllly |
| Sillllly5533669999 | TRUE | 2 | Sillllly5533669999 |
DQ.COLLAPSE_ADJACENT_REPEATED_TYPE
Collapses adjacent repeated types of character.
Parameters:
input (string), text to modify
maximumRepeat (number), the number of characters allowed before collapsing
type (string), type of character to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (string), modified text
| input | collapseNumerics | maximumRepeat | output |
|---|---|---|---|
| TeeestTT | 1 | Letter | TestT |
| TeeeestTT | 1 | LowerCaseLetter | TestTT |
| TeeeestTT | 1 | UpperCaseLetter | TeeeestT |
| TeeEEEstTT | 1 | UpperCaseVowel | TeeEstTT |
| TeeEEEstTT | 1 | LowerCaseVowel | TeEEEstTT |
| TeeEEEstTT | 1 | UpperCaseConsonant | TeeEEEstT |
| TeeEEEssstTT | 1 | LowerCaseConsonant | TeeEEEstTT |
| TeeEEst11223333 | 1 | LetterOrNumber | TeEst123 |
| TeeEEst11223 | 1 | Number | TeeEEst123 |
| TeeEEstTT??..??? | 1 | Punctuation | TeeEEstTT?.? |
| Te EE stTT | 1 | Whitespace | Te EE stTT |
| Te£££st$$TT | 1 | Symbol | Te£st$TT |
| TeeEest | 1 | NonPrinting | TeeEest |
DQ.FILTER_STOP_WORDS
Filters out stop words from a string.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| my noisy string with a lot of stop words | noisy string lot stop words |
DQ.RETAIN_CHARACTERS
Retains given characters in a string, the remaining characters are replaced with the given replacement string.
Parameters:
input (string), text to modify
replacement (string), character to replace with
charactersToRetain (string), characters to not replace
returns (string), modified text
| input | replacement | charactersToRetain | output |
|---|---|---|---|
| this is an input | ? | i | ??i??i?????i???? |
DQ.EXTRACT_CHARACTERS
Extracts a given number of characters from the start or end of a string.
Parameters:
input (string), text to modify
extractLength (string), number of characters to extract
extractFrom (string), the point of the string to extract from, from list:
- Start
- End
returns (string), modified text
| input | extractLength | extractFrom | output |
|---|---|---|---|
| myTestString | 3 | Start | myT |
| myTestString | 3 | End | ing |
DQ.EXTRACT_WORDS
Extracts number of words from a string.
Parameters:
input (string), text to modify
extractLength (string), number of words to extract
extractFrom (string), the point of the string to extract from, from list:
- Start
- End
returns (string), modified text
| input | extractLength | extractFrom | output |
|---|---|---|---|
| my test string | 1 | Start | my |
| my test string | 2 | End | test string |
DQ.REMOVE_HTML
Removes HTML tags from a string.
Parameters:
input (string), text to modify
returns (string), modified text
| input | output |
|---|---|
| Test <p id=myAttr>someText</p> | Test someText |
DQ.STARTS_WITH
Check if a string starts with a given value.
Parameters:
input (string), text to check
checkFor (string), value to check for
returns (boolean), true if starts with value
| input | checkFor | output |
|---|---|---|
| myString | my | true |
| myString | mi | false |
DQ.ENDS_WITH
Parameters:
input (string), text to check
checkFor (string), value to check for
returns (boolean), true if starts with value
| input | checkFor | output |
|---|---|---|
| myString123 | 123 | true |
| myString123 | 456 | false |
DQ.ENSURE_ENDS_WITH
Ensure a string ends with a given value.
Parameters:
input (string), text to modify
suffix (string), value at the end of the string
returns (string), modified text
| input | suffix | output |
|---|---|---|
| test_endString | endString | test_endString |
| test_ | endString | test_endString |
DQ.ENSURE_STARTS_AND_ENDS_WITH
Ensures a string starts and ends with a given value.
Parameters:
input (string), text to modify
topAndTail (string), value to check for
returns (string), modified text
| input | suffix | output |
|---|---|---|
| testString_test_testString | testString | testString_test_testString |
| _test_ | testString | testString_test_testString |
DQ.ENSURE_STARTS_WITH
Ensures a string starts with a given value.
Parameters:
input (string), text to modify
suffix (string), value at the start of the string
returns (string), modified text
| input | suffix | output |
|---|---|---|
| startString_test | startString | startString_test |
| _test | startString | startString_test |
DQ.STARTS_WITH_TYPE
Checks if a string starts with a given type of character.
Parameters:
input (string), text to check
type (string), type of value to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (boolean), true if found
| input | type | output |
|---|---|---|
| test | LowerCaseLetter | true |
| Test | UpperCaseLetter | true |
| Etest | UpperCaseVowel | true |
| eTest | LowerCaseVowel | true |
| Test | UpperCaseConsonant | true |
| test | LowerCaseConsonant | true |
| 1test | LetterOrNumber | true |
| 2test | Number | true |
| .test | Punctuation | true |
| test | Whitespace | true |
| £test | Symbol | true |
| test | NonPrinting | true |
DQ.ENDS_WITH_TYPE
Checks if a string ends with a given type of character.
Parameters:
input (string), text to check
type (string), type of value to check for, from list:
- Letter
- LowerCaseLetter
- UpperCaseLetter
- UpperCaseVowel
- LowerCaseVowel
- UpperCaseConsonant
- LowerCaseConsonant
- LetterOrNumber
- Number
- Punctuation
- Whitespace
- Symbol
- NonPrinting
returns (boolean), true if found
| input | type | output |
|---|---|---|
| testT | Letter | true |
| testt | LowerCaseLetter | true |
| testT | UpperCaseLetter | true |
| testA | UpperCaseVowel | true |
| testa | LowerCaseVowel | true |
| testT | UpperCaseConsonant | true |
| testt | LowerCaseConsonant | true |
| testT | LetterOrNumber | true |
| test1 | Number | true |
| test. | Punctuation | true |
| test | Whitespace | true |
| test$ | Symbol | true |
| test | NonPrinting | true |