HOW TO USE CUSTOM FUNCTIONS TO CLEAN DATA

• • lookup_value: This refers to the text entries that are to be normalized. It can be a reference to one cell e.g. A1 or a range of multiple cells in a single column e.g. A1:A100.

  • stop_array: This is an optional parameter refers to the values that you wish to eliminate from the "lookup_value" entry. It could comprise a group of unwanted words that are separated by commas or punctuation marks that have no delimiters. These values should be contained within a single cell. 

Be careful not to mix unwanted words with punctuation marks in your "stop_array" input.

• • threshold: This is an optional parameter refers to the minimum percentage similarity between parts of the "lookup_value" that you would like to remove and the values in the "stop_array". The default value is 1, which means that only exact matches will be removed from the "lookup_value" entry. 

  • operation: This refers to the operation that you would like the function to run.

Your options are "text" to remove stop words, "punctuations" to remove punctation marks, "diacritics" to remove diacritical marks, "domain" to extract the domain of a URL and "path" to extract the path or a URL.

Removing diacritical marks is the default operation and, therefore, all you need to do is enter the range to process in your formula e.g. =NORMALIZE(A5)

• • left_string: The primary text entry to be compared to "right_string".

  • right_string: The secondary text entry to be compared to "left_string".

• • lookup_value: This determines the specific values whose matches you are looking for within any column of the "table_array" parameter. The "lookup_value" can be a reference to one cell e.g. A1 or a range covering multiple rows e.g. A1:A500.

  • table_array: This determines the range of cells that contain the data to be compared to the values in "lookup_value" and the values to be returned if matches are found. The "table_array" should be a reference to a range e.g. B1:E500.

  • lookup_col: This determines the index of the "table_array" column that contains the values to be compared with "lookup_array". A value of 1 means the function will search the first column in "table_array", a value of 2 will search the second column, and so on. These values must always be greater than 0.

  • index_num: This determines index of the "table_array" column from which data should be returned if matches are found. If the value is 1, then data from the first column of "table_array" is returned. If it is 2, then data from the second column is returned, and so on. These values must always be greater than 0.

  • threshold: This is an optional parameter that determines whether the function will return exact or approximate matches. It represents the level of similarity below which data will not be returned from "table_array". Valid values range from 0 for no filtering to 1 for exact matches and the default value is set to 0.85.

  • merge_col: This optional parameter determines whether the function returns data from all columns of "table_array" or not. If the value is "true", then data from every column in the matching row is returned and "index_num" is ignored. If the value false, then only the column specified by "index_num" is returned. This default value is "false".

• • lookup_value: The value to search in the column of the "table_array". The "lookup_value" argument can be a text entry, a single cell reference or a range of many cells.

  • table_array: The range of cells that contains data that will be returned or merged. The values in the user-specified column of "table_array" are systematically compared to "lookup_value". These values can be text, numbers or anything else.

  • lookup_col: The column index in "table_array" which will be searched by the function.

A "lookup_col" value of 1 searches the first column in "table_array", a "lookup_col" value of 2 searches second column in "table_array", and so on. Values must be greater than 0.

• • index_num: The column index in the "table_array" argument from which the matching value must be returned.

An "index_num" argument of 1 returns values in the first column in "table_array", an "index_num" of 2 returns values in the second column in "table_array", and so on. Values must be greater than 0.

• col_array: This a range of one or more columns from which you intend to extract unique text entries and discard duplicates e.g. A1:C200.

• index_num: This is an index number that represents the column in "col_array" that is going to be analysed for duplicates with the view of extracting unique values. If no input is made, the first column of "col_array" will be analysed.

• threshold: This is an optional variable determines whether ULIST should extract exact or approximate matches. It represents the relative percentage similarity between any two text entries in the target column of "col_array", below which text entries are considered unique and above which text entries are considered to be duplicates. The default value is set at 0.85.

• operation: This determines the method ULIST will use to extract unique values from your dataset.

The values are by_percentage for extracting by percentage similarity and by_sound for extracting by sound similarity. The former is the default function, meaning that ULIST will choose it even if it is not specified in the formula.