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 |
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article