STANDARDIZE DATA BY TEXT SIMILARITY

How to Standardize Datasets

This function can either modify the original dataset in place or leave the original dataset unchanged. Here is a condensed look at what each function mode does:

1. Remove punctuation marks: This will remove all specified punctuation marks from the selected text entries.

2. Remove unwanted words: This will remove all specified words from the selected text entries.

3. Remove diacritical marks: This removes all diacritical marks from text entries.

4. Keep URL domain only: This strips a URL of everything except its root domain e.g. "www.example.com" to "example".

5. Keep URL path only: This strips a URL of everything except its path e.g. "www.example.com/new/users" to "/new/users".

-----

To normalize text entries by removing punctuation marks or removing unwanted words, follow the steps below:

1. Open the function sidebar

   • • Go to Extensions > Flookup Data Wrangler > Transformation functions > Preprocess and standardize in your spreadsheet menu.

2. Select the mode to run

   • • Choose the mode for the function from the drop-down menu.

3. Select the entries to normalize

   • • Highlight the column of text entries to normalize and click "Grab selected range".

4. Select the entries to remove

   • • Highlight the cell containing stop words (comma separated) or punctuation marks (no separation) and click "Grab selected range".

5. Specify the result location

   • • Click an empty cell to indicate where the results should be displayed. This cell should be in any sheet inside that same workbook.

6. Normalize the dataset

   • • Click the "Normalize text entries" button.

-----

To normalize text entries by removing diacritical marks, keeping URL domain or keeping the URL path, follow the steps below:

1. Open the function sidebar

   • • Go to Extensions > Flookup Data Wrangler > > Transformation functions > Preprocess and standardize in your spreadsheet menu.

2. Select the mode to run

   • • Choose the mode for the function from the drop-down menu.

3. Select the entries to normalize

   • • Highlight the column of entries to normalize and click "Grab selected range".

4. Normalize the text entries

   • • Click the "Normalize text entries" button.

-----

NOTES ON STANDARDIZING DATA

• The Threshold parameter is only active when removing unwanted words and you should adjust it at step #5 above. It defines the minimum level of similarity between words to be removed from each text entry in your dataset and the predefined list of unwanted words.

• When removing punctuation marks, Range of entries to be removed should contain values that are not separated by any delimiter e.g. "?,.@/".

• When removing unwanted words, Range of entries to be removed should contain comma separated values e.g. "company, limited, Mr, Mrs".

• This function will process data in the leftmost column of that range if you select more than one column. To normalize text entries from any column within a range, you need to select a single column.

For the Visual Learners

Explore Further

• Match and Merge Data by Text Similarity

• Highlighting Duplicates by Text Similarity

• Removing Duplicates by Text Similarity