Automate 7-Step Data Cleanup Process (i.e., text delimit; re-align data into 1 column, ++)

[skydivetom]

Hello:

In a previous post, expert Rick helped me develop a macro for keyword extraction. The macro works beautifully!! Please see original post at: https://www.excelforum.com/excel-pro...ml#post5477101

Background:
- Cells A2:A5 include some business rules.
- Based on an existing macro "DistinctFieldNamesWithUnderscores", keywords (i.e., those words including an "_" are automatically extracted into column B2:B5: This process works great and does not require any modifications.

Additional information:
1. At this time, certain keywords (e.g., sort_number) are repeated. Please keep in mind that in my actual data set, I have hundreds of records so the amount of repeated words is significantly larger.
2. Also, most values in column B contain multiple keywords separated by a ";".
3. Ultimately, I would like to have my output reflecting:
3a: Distinct values
3b: ... in distict cells
3c: ... no trailing/leading spaces before the keyword

7-Step Cleanup Process:
Now, to demonstrate how I clean up the data, I have included a secondary XLS "7-Step Cleanup Process". This XLS summarizes the individual steps that I am currently performing in a manual process. Essentially, it includes steps such as text delimitation, moving values across several columns into a single column, removing null and header values, removing trailing spaces, and sorting the data.

Here's what I need some help with:
- Based on the 2nd spreadsheet, is there a way to automate the cleanup process for values B2:B5 (in this spreadsheet) where I end up with a clean list as shown on tab "Desired Data Output".
- If so, what would the VBA/macro look like?

Thank you,
EEH