Clean Data#

Overview#

Clean Data is a tool for data cleaning. It supports replacing characters, stripping data, changing case, and removing formatting. The user can choose one or multiple steps in supported functions for different purposes.

Videos#

Examples#

Here is a step-by-step example of using Clean Data to manipulate data.

In this sample dataset there are a few typos, and we aim to make the following changes:

  • Change “9&3 HO” to “9_3 HO” in D3

  • Make all data lower case

  • Remove data formatting in column B (B2:B7)

  • Get rid of the asterisk sign in C7

  1. Prepare an Excel sheet with data to manipulate (A2:E7 in this case since we do not need to make changes to the header row)

  2. Open Finplicity, and then click Data Basics > Clean Data. The form of Clean Data will appear as below.

  3. Select the data range to manipulate. In this example, select A2:E7. And select an output location. In this example, select A9

  4. Click “Next” and select the old and new character for replacing character. In this example, input the old character “&” and the new character “_.”

  5. Click “Next” and select characters to be stripped. In this example, check “Asterisk (*).” Note that you can have multiple selections if needed.

  6. Click “Next” and select the case you want to apply. In this example, select “Lower” for lower case.

  7. Click “Next” and select options for removing formatting. In this example, check “Remove all Formatting.”

  8. Now you have finished all the steps in this form, and you can click “Run” to clean data or click “Save” to save this task. The cleaned data will be shown in the output location.

FAQ#

  • What if I only need some of the data cleaning steps in Clean Data?

For unwanted steps, you can leave the corresponding pages unchanged and click “Next” to skip, but make sure you select at least one method in this form.

  • Can I select multiple items in the checklist?

Yes. You can input multiple old characters to be replaced in step 2 or have multiple selections in step 3 and step 5.

  • What is the order of executing each step of data cleaning?

Data cleaning is done step by step following the order of pages in the form, i.e., starting from replacing characters, then stripping data, then changing case, and finally removing formatting. Sometimes this certain order is not capable of the user’s request, for example, to change case before replacing character. In this case, the user can run this form multiple times or create a workflow of multiple tasks in the workflow manager (see Task Workflow Manager).

On this page