User Tools

Site Tools


additional_custom_functions

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
additional_custom_functions.txt · Last modified: 2022/08/05 13:26 by robert.tootill